How to Copy worksheet to new workbook with dynamic name?
Greetings All,
We're trying to create a macro to copy a worksheet to another workbook instead of copying to the default name of 'Book1' it should copy to the new workbook with the name of 'ExportDataMMDDYY where MMDDYY is the current month day and year. The big questions are, 1) is it possible to have it create the new workbook with a name such as that above, without first saving the workbook? (just as book1.xls is the default book name that hasn't yet been saved) and 2) how do I give the new workbook a name of ExportDataMMDDYY? At the risk of being redundant, here's a quick practical example. The source workbook (SourceBook) will have 15 worksheet tabs. The user may run the macro on any one of the tabs at a time. Each time the macro is run it SHOULD copy the contents of the active worksheet into the new workbook with the name ExportData123003 and then return to the SourceBook. The code that I have managed to create so far is listed below. What I can't figure out is how to have it name the new book with the file name format indicated above. Any help would be HUGELY appreciated. Thx, Ashleigh ---------My Sample Code Follows--------------- Sub GoatFeet() CurrentBookName = ActiveWorkbook.Name ' Copy the active worksheet to a new workbook, ideally it would copy to a workbook ' titled 'ExportDataMMDDYY where MMDDYY is the current month day and year ActiveSheet.Copy ' Remove all formulas within the worksheet by copying and pasting only values Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ' Return to the original workbook. ' Workbooks(CurrentBookName).Activate |
How to Copy worksheet to new workbook with dynamic name?
Here You a
Sub ExportDataToNewWrkBook() Dim wbk As Workbook Dim wsh As Worksheet Dim sCell As String Set wsh = ThisWorkbook.ActiveSheet sCell = ActiveCell.Address wsh.Cells.Select wsh.Cells.Copy Set wbk = Workbooks.Add With wbk .Worksheets(1).Range("A1").Select .Worksheets(1).Paste .SaveAs "D:\ExportData" & Format(Date, "mmddyy") & ".xls" End With wsh.Activate wsh.Range(sCell).Select End Sub -----Original Message----- Greetings All, We're trying to create a macro to copy a worksheet to another workbook instead of copying to the default name of 'Book1' it should copy to the new workbook with the name of 'ExportDataMMDDYY where MMDDYY is the current month day and year. The big questions are, 1) is it possible to have it create the new workbook with a name such as that above, without first saving the workbook? (just as book1.xls is the default book name that hasn't yet been saved) and 2) how do I give the new workbook a name of ExportDataMMDDYY? At the risk of being redundant, here's a quick practical example. The source workbook (SourceBook) will have 15 worksheet tabs. The user may run the macro on any one of the tabs at a time. Each time the macro is run it SHOULD copy the contents of the active worksheet into the new workbook with the name ExportData123003 and then return to the SourceBook. The code that I have managed to create so far is listed below. What I can't figure out is how to have it name the new book with the file name format indicated above. Any help would be HUGELY appreciated. Thx, Ashleigh ---------My Sample Code Follows--------------- Sub GoatFeet() CurrentBookName = ActiveWorkbook.Name ' Copy the active worksheet to a new workbook, ideally it would copy to a workbook ' titled 'ExportDataMMDDYY where MMDDYY is the current month day and year ActiveSheet.Copy ' Remove all formulas within the worksheet by copying and pasting only values Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ' Return to the original workbook. ' Workbooks(CurrentBookName).Activate . |
All times are GMT +1. The time now is 06:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com