VBA code to save sheet as Workbook example code will help us to save a specific sheet as new workbook. We can use Copy and SaveAs methods of Workbook to copy and save the sheet into new workbook. In this example we will see how to save Active Sheet and specific worksheet as new excel file using VBA. And this code should work for all the version of Microsoft Excel 2003, Excel 2007, Excel 2010, and Excel 2013.
VBA code to save Sheet as New Workbook
Here is the Example VBA syntax and Example VBA code to save a Sheet as New Workbook. This will help you to how to save a worksheet as New Workbook using VBA.
VBA Save Sheet as Workbook: Syntax
Following is the VBA Syntax and sample VBA code to Save a Sheet as Workbook using VBA. We are using the Copy and SaveAs methods of the Excel Workbook object.
WORKBOOK1.SHEETS(“WORKSHEET1).COPY BEFORE:= WORKBOOK2.SHEETS(1)
WORKBOOK. SAVEAS “FILE PATH TO SAVE”
Here workbooks can be ActiveWorkbook, ThisWorkbook or a workbook assigned to an object.
ActiveWorkbook. Workbook1 is your source workbook and Worksheet1 is your sheet to copy. And Workbook2 is the destination sheet and sheets(1) and before key words tells Excel to copy the worksheet before the first sheet of workbook2.
Here you can observe that we are copying the worksheet in the first statement. We are using Copy method of workbook to copy the worksheet. Then we are saving the file in as specific location using SaveAs method of Workbook.
Save Worksheet as New Workbook using VBA: Examples
The following VBA code is to Copy the worksheet into new workbook and Save in a specific folder.
Sub sb_Copy_Save_Worksheet_As_Workbook() Dim wb As Workbook Set wb = Workbooks.Add ThisWorkbook.Sheets("Sheet1").Copy Before:=wb.Sheets(1) wb.SaveAs "C:\temp\test1.xlsx" End Sub
Instructions to run the vba code to save a worksheet as new Excel Workbook
Please follow the below steps to execute the vba code to save the worksheet as new excel file.
Step 1: Open any existing Excel workbook
Step 2: Press Alt+F11 – This will open the VBA Editor
Step 3: Insert a code module from then insert menu
Step 4: Copy the above code and paste in the code module which have inserted in the above step
Step 5: Change the code as per your requirement
Step 6: Change the file path as per your testing folder
Step 6: Now press F5 to execute the code
Now you can observe that your worksheet is saved as new Excel workbook in the specified folder.
Explained VBA Code to Save worksheet as new Workbook
‘Starting a procedure to save a worksheet as new workbook
‘Declaring a variable as workbook to store the newly creating workbook
Dim wb As Workbook
‘adding a new workbook and seting to wb object
Set wb = Workbooks.Add
‘Copying a worksheet from ThisWorkbook into newly creadted workbook in the above statement
‘Saving the newly created Excel workbook into required folder with specific workbook name
‘Ending sub procdure to save a worksheet as new workbook
Save Active Sheet as New Workbook using VBA: Examples
The following VBA code is to Copy the active worksheet into new workbook and Save in a specific folder.
Sub sb_Copy_Save_ActiveSheet_As_Workbook() Set wb = Workbooks.Add ThisWorkbook.Activate ActiveSheet.Copy Before:=wb.Sheets(1) wb.Activate wb.SaveAs "C:\temp\test3.xlsx"End Sub