Copy Worksheet To New Workbook & Save
Oct 18, 2006
I have a workbook with a worksheet named "Report"... this sheet and it's macros generate a report from data on another sheet. I'm adding a button to copy the (filled) Report sheet to a new workbook, rename the new sheet in the new workbook "Rep[Name]", then save and close the new workbook. I've recorded a maco of me doing these tasks manually, but what's recorded seems very generic, and does not seem to specify what's really occurring...
For example (in part);
> Rightclick on the Report tab
> Move or Copy
> To Book: (new book)
> [x]Create a Copy
> [OK]
yields:
Sub Macro1()
Sheets("Report").Select
Sheets("Report").Copy
End Sub
This recorded macro does not specify (new book), or Before:"Sheet1" as was the result of my actions... and when replayed back, only makes a copy in the active workbook, before the active sheet...
View 4 Replies
ADVERTISEMENT
Oct 19, 2011
How to copy the entire worksheet into a new workbook and save the file to a specific directory with the following filename format ("exceptions191011 - ie. the word exceptions followed by today's date in ddmmyy format).
Here is what I've got so far:
Code:
Sub SaveAs()
Dim FName As String
Dim FPath As String
FPath = "G:Exceptions"
FName = "Exceptions" & Format(Date, "ddmmyy") & ".xls"
Sheets("DataSort").Copy
ThisWorkbook.Sheets("Sheet1").SaveAs Filename:=FPath & "" & FName
End Sub
How to add the following condition that if a file already exists with that name then to bring up an error message stating the file already exists? As well as save the new workbook, it appears to close the original workbook - can I stop this?
View 3 Replies
View Related
Jun 17, 2014
I have been trying to edit a code which previously saved a copy in a new workbook to a specific folder/path. (Additionally it copies and clears some figures, but this is working as it should.)
However I would like the copy to be saved at the same location as the original workbook, regardless of the path the original workbook is saved.
I.e if I need to move the workbook containing the code to a new folder/location, when using the macro, the new copy should be stored in the same folder/location as the original one.
For now it is only saving the copy into "My Documents"
Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 16-02-2009 by ceng
'
Sheets("Bunker ROB").Select
Sheets("Bunker ROB").Copy
ActiveWorkbook.SaveAs Filename:= _
ActiveWorkbook.Path & Range("D3"), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
[Code] ........
View 2 Replies
View Related
May 8, 2014
I have a sheet full of data.All this long I had written the code to save the selected cells as a PDF.But now I want it to be saved as a separate file. In this case, I believe, first the VBA will have to open a new workbook. Then copy paste this sheet onto one of the sheets in the workbook . then save the .XLM file.
[Code] ..
View 7 Replies
View Related
Apr 19, 2007
I have a save button on a user form that saves a workbook
Private Sub SaveBookbtn_Click()
' SaveAs using data from workbook cells
Dim ThisFile As String
Dim TheFilename As String
ThisFile = Sheets("Sheet1").Range("A2").Value
TheFilename = Application.GetSaveAsFilename _
(ThisFile, filefilter:="Excel Files, *.xls")
If TheFilename = "False" Then Exit Sub
ThisWorkbook.SaveAs TheFilename
ThisWorkbook.Close
End Sub
But what I would like to do is save just "sheet1", and not the whole workbook, using ThisFile as the name to save it under - where I can choose which directory it can go in. I have tried to modify the code to get the desired results but I can't seem to crack it. Can the above code be modified to do the job or am I going to have to start from scratch?
View 2 Replies
View Related
Nov 22, 2011
I have excel contains multiple sheet. I want to save all sheets as in different workbook in specific folder.
e.g. Workbook "Report" has 3 worksheet i.e. A, B & C By running macro all 3 sheets should be saved as different workbook on "C:Report(folder)"
C:Report(folder)A.xls
C:Report(folder)B.xls
C:Report(folder)C.xls
View 1 Replies
View Related
Oct 30, 2006
I want to find a code to lock all the cells with data once a button is pressed and with the same button click i want the workbook to be saved.
View 5 Replies
View Related
Nov 21, 2007
I have a file consisting of many sheets. I need a macro that saves the file as a normal excel file, that is just a normal save as function. This I have. But then I also want the macro to save one of the sheets as a .txt file. This is what I got from reading this outstanding forum. But this saves all my sheets as seperate .txt files
Sub wsToText()
Dim ws As Worksheet
Application. ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
Sheets(ws. Name).Select
Sheets(ws.Name).Copy
ActiveWorkbook.SaveAs Filename:= _
"C:Documents and Settings1kitvelDesktop" & ws.Name & ".txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Close
ThisWorkbook.Activate
Next
End Sub
View 2 Replies
View Related
Apr 21, 2012
I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.
How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?
View 1 Replies
View Related
Nov 24, 2011
Intention is to (automatically) fully protect each completed worksheet/workbook on "save"/"save as" in MS Excel 2007... Is this possible by means of a macro? Which one? Saving in a ".xlsm" format is required?
View 9 Replies
View Related
May 20, 2009
I need save my workbook as copy to another directory, ( C: ) with VBA code
View 9 Replies
View Related
Dec 4, 2009
I know that the line in my code Worksheets.Copy is NOT what I need to have to make this work, but with it it's the closest I could get to what I want.
I want the user to be able to click the SAVE command button, be allowed to either accept or change the default save options, save the current workbook and then go right back to the form that has the SAVE command button. Sounds simple, I know, but when you don't know the right commands to save the WORKBOOK, not just the WORKSHEETS, it becomes a P.I.A.
I know there's a lot there, but here's my code, which is just a slightly altered version of the code by Ron de Bruin:
Private Sub SaveData_Click()
'If no date has been selected then don't allow SAVE
If PubDateLabel.Caption = "" Then
MsgBox "Publication Date not set. You must select a date before saving.", vbCritical, "No Publication Date!"
Exit Sub
End If
View 9 Replies
View Related
Sep 8, 2006
i need to copy from one workbook to another at present i have formulars in columns a b c d e g in f and h the is data that could go to f and h100 these are not formulars
i need to be able to copy the data from a1 to the last cell with data in it in column A to the same row in column h
View 9 Replies
View Related
Mar 26, 2013
have seen macros that create a seperate sheet & save by tab number etc, & wonder if its possible to save each sheet in a workbook as the value defined in each sheets cell A2. is there a vb macro available to do that ? if so where ?
View 9 Replies
View Related
Sep 22, 2011
I have a Workbook with around 10 worksheets on, each worksheet is a form.
And when one form is completed we have to save a backup. So i want to assign a macro to a button on a worksheet so when clicked it will save a copy of that worksheet ONLY to a destination and not all the other worksheets along with it.
View 9 Replies
View Related
Mar 9, 2012
I'm looking for a macro that can do the following:
Contact
First Name
BD
Case
XXXXXXX
Aaron
A
XXXXXXXXXXXXXXX
XXXXXXXX
Abigail
C
XXXXXXXXXXXXXXX
XXXXXXXXXXXXXX
Accounts
D
XXXXXXXXXXXXXXX
XXXXXXXXXXXXXX
Adam
C
XXXXXXXXXXXXXXX
Filter by BD 'C'
Copy all rows containing 'C' to new workbook.
Save new Workbook as 'Mailing C.xls' and close to specific flolder.
View 7 Replies
View Related
Jun 23, 2014
I have managed to pull together code that does the required task - save two sheets from a work book in to a new workbook on to a dorectory each day. However I woul;d liek to paste special the values and cannot figure our how to reference that on the below:
'saves text file in day on day folder
Dim WS As Worksheet, CheminDest As String, fNAME As String
'create directories as needed
On Error Resume Next
CheminDest = "T:DMRatesReportsChecks" & Year(Date) & ""
[Code] ..........
View 3 Replies
View Related
Aug 16, 2007
Is it possible to use VBA code to save a copy of your workbook while removing all formulas and replaceing them with values and formats only? I know how to save and rename, but can;t find the command for a "Static Copy".
View 9 Replies
View Related
Aug 27, 2007
I'm trying to save a copy an excel file in the same directory of the original file. The code is pretty standard but it does not work if the file is stored in C: (only c: ) Is that a bug or am I doing something wrong? Anyways here is the code in case someone is interested in trying it out:
Sub CreateCopy()
ChDrive ThisWorkbook.Path
ChDir ThisWorkbook.Path
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls", _
InitialFileName:="CMS_" & Format(Now(), "mm-dd-yyyy"))
If fileSaveName <> False Then
MsgBox "Backup copy saved as: " & fileSaveName
End If
End Sub
View 5 Replies
View Related
Sep 11, 2007
I have a workbook that has worksheet called "Data". In that workbook, there is some codes for the event of "workbook_beforeprint". My question is whether i could create a copy of the "master" workbook and delete all the unwanted sheets but "Data" sheet using VBA. There are some codes of creating a new workbook but i suppose i need to the exact copy of the current workbook using VB
View 3 Replies
View Related
Jun 14, 2008
I want to have a user press a button on a worksheet page to save a copy of the workbook (and be able to name it and choose where to save it to) without closing or modifying the original. Note that if you "save as" manually it renames the workbook and closes the original.
View 7 Replies
View Related
Aug 7, 2008
I have a report which I have written a macro to reformat, I then want to copy the sheet (Remake) containing the report into a new workbook as the only sheet in the new workbook. I want the sheet name to be changed from "Remake" to "Duty to Consider" and I want the new workbook to save itself automatically as "Duty to Consider - Month/Year" - in this case it would be "Duty to Consider - August 2008".
I think that a Pop Up box which lets me input the Month and Year of the report I am working on would be useful in naming the new workbook, if this box popped up when I click the button which runs my Macro that'd be great. Here is the code I am using to reformat my report. This works fine I just need the changes I mentioned. Application. ScreenUpdating = False 'this will turn on screen updating, making the macro run faster
'This will select the DTC Tab and remove rows 1-4
Sheets("DTC").Select
Rows("1:4").Select
Range("A4").Activate
Selection.Delete Shift:=xlUp
'This will add a filter to the data to remove duplicate rows
Range("A1").Select
Range("A1:J1307").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
'This will copy the data on the DTC tab and copy it into the Remake Tab
Cells.Select.....................
View 2 Replies
View Related
Jan 21, 2010
I have this macro I recorded and pieced together from info I found on the net. It works the first time you run it but then it dies because the next new workbook is not "book1" what I would prefer is to have it save the new workbook as the value of sheet2 F2 and "Product Bulletin" so it would look like "##### Product Bulletin" but I don't know how to write it.
View 6 Replies
View Related
Oct 28, 2008
I have an Excel invoice set up and working well. It does a bunch of things with macros - e.g. on save it increments the invoice number well as creates a jpeg screenshot for the invoice archives. I have added an additional worksheet (titled 'VAT') to the workbook. The new 'VAT' worksheet has five simple columns; Invoice no, Subtotal, VAT, M.O.T. and Total.
What I need:-
On saving the workbook I would like to add a macro function that copys the final contents of the Invoice no (H2), Subtotal (C37), VAT (C38), M.O.T. (F38) and Total (I38) cells from the 'Sales Invoice' worksheet to the newly created 'VAT' worksheet in the respective columns. I would like this to be cumulative, i.e. continue to add the contents of the afore mentioned cells to the appropriate columns in the 'VAT' worksheet every time the invoice is saved. I would also like to have the Subtotal, VAT, MOT and Total columns summed and outputted in a cell of their own - but hopefully I can handle that.
View 2 Replies
View Related
Mar 23, 2009
I have code that i use to copy a worksheet of information in one workbook to a worksheet in another workbook. All i need is some guidance on how to copy a second worksheet from workbook 1 to a second worksheet in workbook 2. Should be fairl straight forward.
In the below i am copying the sheet Phone_data to a second work book sheet also called Phone_Data, i would like to include in the same proccess a sheet called Sur_Data from workbook 1 copy to a sheet called Sur_Data_R in the second. All the other features like find next empty row also apply.
View 2 Replies
View Related
Sep 7, 2013
I need to save an excel workbook when a "backup" button is pressed to a folder named "Backups". I want the name to be the "date" (09/07/2013) only and I want it to automatically overwrite anything previously saved for that day.
Book1.xlsm
View 2 Replies
View Related
Apr 27, 2012
Code, couldn't make it work with VB HTML Maker. I'm new with VBA Basically my idea is when someone open default file, insert data, try to save file, macros save workbook with inserted data as another file (*.xlsx) and make pdf in another folder. Pdf part is working fine, but with second part just can't find right option.
Pdf part
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim Name As String
Dim i As Integer, j As Integer
Name = ThisWorkbook.Path & "PDF Archive" & ActiveSheet.Range("F6") & " Invoice " & ActiveSheet.Range("B11")
[Code]...
Excel part
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ActSheet As Worksheet
Dim ActBook As Workbook
Dim CurrentFile As String
Dim NewFileType As String
Dim NewFile As String
[Code]...
View 1 Replies
View Related
Jan 12, 2013
I'm using a code to copy text from a word doc into a spreadsheet. It does it all right, but when it comes to saving, it does not save the open worksheet and prompts me to save as a copy. If I do so, nothing is changed in the original file and the copy file has the information copied from the word file.
This is the code I'm using to write and save the spreadsheet.
...
(VBA in WORD)
Set wdApp = Excel.Application
Workbooks.Open FileName:="C:UsersljsDocumentsword2excel.xls"
Workbooks("word2excel.xls").Activate
Worksheets("Plan1").Activate
Worksheets("Plan1").Range("A2").Value = DOCname
Worksheets("Plan1").Range("B2").Value = CarNo
Worksheets("Plan1").Range("C2").Value = WordNo
ActiveWorkbook.Save
ActiveWorkbook.Close
...
View 1 Replies
View Related
Feb 28, 2014
I want to take the current sheet and copy to a new workbook (keeping the tab name) and then save as the tab name (xlsl) and close.
I've found ones that do a Saveas box or ends up saving as "Sheet1," "Sheet2" etc but can't seem to find one that keeps tab name and saves.
Here is the one I was using, but it saves all tabs not just selected:
Code:
Sub CopySheet()
Dim fname As Variant, ws As Worksheet, wb As Workbook
Dim nCol As Integer, iCol As Integer
[Code].....
View 1 Replies
View Related
Mar 12, 2014
I've got a kind of "menu" sheet that will pull up various reports from Excel spreadsheets. Rather than having the user work with the live data, the menu opens the file and saves it as a copy, closing the original. This is working fine.
The tweak I want to add is, since the user is looking at a copy rather than the live data, I don't want them to save when closing. Is it possible to programatically add a bit of code to the new copy to handle this?
Here's a sample of one of the menu button's code:
Code:
Private Sub CommandButton18_Click()
Dim wb As Workbook
Set wb = Workbooks.Open(Filename:="J:Z PII OvensRack Repair TrackingRack Repair List.xlsx")
With wb.Worksheets("Sheet1")
.Unprotect Password:="pass"
ActiveWorkbook.Worksheets("Sheet1").Copy
End With
[Code].....
View 7 Replies
View Related
