VBA to Copy Sheet to New Workbook

If you often need to copy a sheet to a new workbook, using VBA can be quicker and more efficient than doing it manually.

In this tutorial, I will show you five examples of using VBA to copy a sheet to a new workbook created in VBA using the Worksheet.Copy method.

Worksheet.Copy Method in VBA

The ‘Worksheet.Copy‘ method copies a sheet to another location in another workbook or the current workbook.

The syntax of the method:

expression.Copy (Before, After)

where, expression is a variable representing a worksheet object.

The ‘Before’ and ‘After’ arguments are optional. The ‘Before’ parameter indicates the sheet before the code will place the copied worksheet, and the ‘After’ parameter specifies the sheet after which the code will place the copied worksheet.

If you don’t specify the arguments, Excel creates a new workbook containing the copied sheet.

VBA to Copy a Specific Sheet As Is to a New Workbook

Suppose you want to copy a sheet named ‘Annual Sales’ precisely as it is from the current workbook to a new workbook.

You can use the below VBA code to copy the sheet ‘Annual Sales’ exactly as it is to a new workbook:

Sub CopySheetToNewWorkbook()
Dim sourceWs As Worksheet
Set sourceWs = ThisWorkbook.Sheets("Annual Sales")
sourceWs.Copy
End Sub

Explanation of the Code

The following is an explanation of the third and fourth lines of the code:

  1. Set sourceWs = ThisWorkbook.Sheets(“Annual Sales”): This statement assigns a reference to the sheet named ‘Annual Sales’ in the current workbook (ThisWorkbook) to the variable ‘sourceWs.’

Note: In this example, we have hardcoded the worksheet name. If you want to copy the active worksheet, modify this statement to ‘Set sources = ActiveSheet.’

  1. sourceWs.Copy: This line uses the ‘Copy’ method to create a new workbook and copy the worksheet referred to by the variable ‘sourceWs’ to it.  The ‘Copy’ method duplicates the entire worksheet, including its content, formatting, macros in the sheet module (if any), and other associated properties.
Also read: How to Delete a Sheet in Excel Using VBA

VBA to Copy a Sheet As Values to a New Workbook

Suppose you have the active sheet containing a dataset with formulas and want to copy it to a new workbook as values.

Note: When you copy a sheet as values, the formulas are removed, and only the values remain while formatting and objects on the sheet are preserved.

You can use the VBA code below to copy the active sheet as values to a new workbook:

Sub CopySheetasValues()
Dim sourceWs As Worksheet
Dim newWb As Workbook
Dim newWs As Worksheet
Set sourceWs = ActiveSheet
Set newWb = Workbooks.Add
sourceWs.Copy Before:=newWb.Sheets(1)
Set newWs = newWb.Sheets(1)
With newWs.UsedRange
    .Copy
    .PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False   
End Sub

Explanation of the Code

The VBA code creates a new workbook, copies the active sheet to that new workbook, and then pastes the used range of the copied sheet as values in the new workbook.

The following explains some of the code’s lines:

  1. Set sourceWs = ActiveSheet: This line assigns the currently active sheet that will be copied to the new workbook to the variable ‘sourceWs.’
  2. Set newWb = Workbooks.Add: This statement creates a new workbook and assigns it to the variable ‘newWb.’
  3. sourceWs.Copy Before:=newWb.Sheets(1): This line copies the active sheet to the new workbook before the first default sheet in that workbook. This step makes the copied sheet the first sheet in the new workbook.
  4. Set newWs = newWb.Sheets(1): This line assigns a reference to the first sheet in the new workbook to the variable ‘newWs.’
  5. newWs.UsedRange Copy: In the ‘With End With’ block, this statement copies the entire used range of the new worksheet to the clipboard.
  6. newWs.UsedRange PasteSpecial Paste:=xlPasteValues: In the ‘With End With’ block, this line pastes the clipboard’s contents as values in the same location, effectively removing any formulas and retaining only the values.
  7. Application.CutCopyMode = False: This line clears the clipboard and exits the cut or copy mode. 
Also read: VBA to Copy Range to Email Body

VBA to Copy Sheet to a New Workbook and Save the New Workbook as CSV

Suppose you want to copy the active sheet in the current workbook to a new workbook and save the new workbook as a comma-separated file (CSV) to a specific path.

You can use the below VBA code to perform the task:

Sub CopySheetandSaveasCSV()
Dim newWorkbook As Workbook
Dim csvPath As String
ActiveSheet.Copy
Set newWorkbook = ActiveWorkbook
csvPath = "C:\Archived Reports\Revenue.csv"
newWorkbook.SaveAs Filename:=csvPath, FileFormat:=xlCSV, _ CreateBackup:=False
newWorkbook.Close SaveChanges:=False
End Sub

Explanation of the Code

The subroutine copies the active sheet to a new workbook, saves that new workbook as a CSV file with the specified path, and closes the new workbook without saving any additional changes.

The following explains some statements of the code:

  1. ActiveSheet.Copy: This line copies the active sheet to a new workbook.
  2. Set newWorkbook = ActiveWorkbook: This statement sets the ‘newWorkbook’ variable to reference the newly created workbook containing the copied sheet.
  3. csvPath = “C:Archived ReportsRevenue.csv”: This line specifies the path and filename for the CSV file. You can modify this path to suit your requirements.
  4. newWorkbook.SaveAs Filename:=csvPath, FileFormat:=xlCSV, CreateBackup:=False: This line saves the new workbook as a CSV file with the specified path and filename.
  5.  newWorkbook.Close SaveChanges:=False: This line closes the new workbook without saving changes.

Note: CSV files do not support formatting, formulas, or other features beyond the primary data.

VBA to Copy a Specific Sheet to a New Workbook and Rename it

Suppose you want to copy a sheet named ‘Annual Sales’ from the active workbook into a new workbook and rename the new sheet ‘Regional Sales Summary.’

The following code will do the job for you:

Sub CopySheetRename()
Dim sourceWs As Worksheet
Dim newWb As Workbook
Dim wsName As String
Set sourceWs = ThisWorkbook.Sheets("Annual Sales")
wsName = "Regional Sales Summary"
sourceWs.Copy
Set newWb = ActiveWorkbook
newWb.Sheets(1).Name = wsName
End Sub

Explanation of the Code

The code copies a sheet named ‘Annual Sales’ from the current workbook to a new workbook and renames the first sheet of the new workbook to ‘Regional Sales Summary.’

Here’s an explanation of some of the statements in the code:

  1. Set sourceWs = ThisWorkbook.Sheets(“Annual Sales”): This line sets the ‘sourceWs’ variable to reference the sheet named ‘Annual Sales’ in the current workbook.
  2. wsName = “Regional Sales Summary”: This statement assigns the string ‘Regional Sales Summary’ to the ‘wsName’ variable.
  3. sourceWs.Copy: This line copies the source worksheet, ‘Annual Sales,’ to a new workbook.
  4. Set newWb = ActiveWorkbook: This statement sets the ‘newWb’ variable to reference the newly created workbook (the currently active workbook).
  5. newWb.Sheets(1).Name = wsName: This line changes the name of the first sheet in the new workbook to the value stored in the ‘wsName’ variable, which is ‘Regional Sales Summary.’
Also read: VBA to Copy Range to Another Sheet

VBA to Copy Sheet to a New Workbook and Save the New Workbook to a Specific Path

Suppose you want to copy the active sheet in the current workbook to a new workbook and save the new workbook to a specific path as ‘Archived Sales Report.’

You can accomplish the task using the below code:

Sub CopySheetAndSaveNewWorkbook()
Dim sourceWs As Worksheet
Dim newWb As Workbook
Dim savePath As String
Set sourceWs = ActiveSheet
savePath = "C:\Archived Reports\Archived Sales Report.xlsx"
sourceWs.Copy
Set newWb = ActiveWorkbook
newWb.SaveAs Filename:=savePath
newWb.Close
End Sub

Explanation of the Code

Here’s an explanation of some of the code’s statements:

  1. Set sourceWs = ActiveSheet: This line assigns the active sheet to be copied to the new workbook to the variable ‘sourceWs.’
  2. savePath = “C:Archived ReportsArchived Sales Report.xlsx”: The specified string is assigned to the ‘savePath’ variable. This is the path and filename where the new workbook will be saved. You can modify this path to suit your requirements.
  3. sourceWs.Copy: This statement copies the active sheet to a new workbook.
  4. Set newWb = ActiveWorkbook: Sets the ‘newWb’ variable to reference the newly created workbook (the currently active workbook).
  5. newWb.SaveAs Filename:=savePath: Saves the new workbook with the specified filename and path.
  6. newWb.Close: Closes the new workbook. You can leave out this statement to keep the workbook open.

Note: Ensure that the folder where you want to save the workbook exists; otherwise, VBA will throw a runtime error ‘1004.’

VBA to Copy Sheet to New Workbook

In this tutorial, I showed you some examples of using VBA to copy a sheet to a new workbook. I hope you found the tutorial helpful.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment