How do I save a worksheet to a new workbook?
47,296
Here is a macro showing/doing what you are asking for. Change the workbook name as well as sheet name as needed:
Sub test()
Dim wb, wbnew As Workbook
Dim ws As Worksheet
Dim filename As String
Set wb = Workbooks(2) 'Name of the workbook you are copying from
Set ws = wb.Sheets("Sheet1") 'Name of sheet you are copying
filename = ws.Range("A11") & "_" & ws.Range("B11")& ".xlsm"
'adds new workbook
Set wbnew = Workbooks.Add
wb.Activate
'copies sheet to new workbook
wb.Sheets("Sheet1").Copy Before:=wbnew.Sheets(1)
'Save as dialog box to save as excel file
Application.Dialogs(xlDialogSaveAs).Show filename
wbnew.Close
End Sub
Related videos on Youtube
Author by
MrDoe
Updated on May 30, 2020Comments
-
MrDoe almost 4 years
I am trying to save a single worksheet to a new workbook (VBA should create a new workbook).
The code is running and the new workbook is popping up with the needed data and the save as dialogue window also opens but when I select the name and save the file it is no where to be found the the new workbook stays open as (Book1 or whatever auto populates).
I would like for a Save As name to auto populate when the dialogue window opens and have it referencing to two cells (i.e. $A$11, $B$11) in the original workbook.
Here is my VBA:
Option Explicit Sub SaveConsolidatedDemandData() ' Macro2 Macro 'Dim NameFile As Variant 'With Worksheets("SO1") 'NameFile = .Range("M3") & "_" & .Range("C11") & "_" & .Range("B22") & ".xls" 'End With Application.ScreenUpdating = False ' ' Sheets("Consolidation").Select Sheets("Consolidation").Copy Application.GetSaveAsFilename Application.ScreenUpdating = True End Sub
-
Chuck almost 7 yearsCheck out this link: copy all workbook sheets to a new workbook VBA
-
-
TylerH about 4 yearsThis gives a 'subscript out of range' error for me. I had to use
After
instead ofBefore
. when copying to the new workbook.