How to copy an Excel sheet to a new workbook using VBA?

To copy an Excel sheet to a new workbook using VBA, you can use the following steps. This involves writing a VBA macro that performs the task of copying a worksheet and subsequently saving it in a new workbook. Here is a basic example:

Sub CopySheetToNewWorkbook()
    
    Dim ws As Worksheet
    Dim newWorkbook As Workbook

    ' Change "Sheet1" to the name of your sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Copy the sheet to a new workbook
    ws.Copy

    ' The new workbook with just the copied sheet becomes the ActiveWorkbook
    Set newWorkbook = ActiveWorkbook

    ' Optionally, save the new workbook
    ' newWorkbook.SaveAs "C:PathToYourDirectoryNewWorkbook.xlsx"
    
    ' Optionally, you can close the new workbook
    ' newWorkbook.Close SaveChanges:=True

End Sub

Open the Developer Tab:

If you don’t have the Developer tab enabled in Excel, go to `File` > `Options` > `Customize Ribbon`, and check the `Developer` option on the right side to enable it.

Access the VBA Editor:

Click on the `Developer` tab, then click on `Visual Basic` to open the VBA Editor.

Insert a Module:

In the VBA Editor, click on `Insert` > `Module` to insert a new module where you can write your code.

Write the VBA Code:

In the module, type or paste the following code:

Customize the Code (Optional):

Replace `”Sheet1″` with the actual name of the sheet you want to copy.
Specify a file path and name in the `SaveAs` method if you want to save the new workbook automatically.
Uncomment `newWorkbook.Close SaveChanges:=True` if you want the new workbook to close automatically after saving.

Run the Macro:

Close the VBA editor and return to Excel.
Press `ALT + F8` to open the “Macro” dialog.
Select `CopySheetToNewWorkbook` from the list and click `Run`.

This macro copies the specified worksheet to a new workbook. If you uncomment the `SaveAs` and `Close` lines, it will also save and close the new workbook. Be sure to handle any exceptions or errors as needed, such as verifying sheet names or ensuring paths are correctly specified.

facebooklinkedin