VBA Copy Worksheet

What is Excel VBA Copy Worksheet?

Excel VBA Copy Worksheet is a feature of Visual Basic for Applications (VBA) that allows you to duplicate worksheets within the same or between different workbooks. It provides a programmatic way to automate creating copies of worksheets, enabling you to streamline repetitive tasks, manipulate data, and perform various operations on the copied worksheets. Let us look at an example. In this example, we will copy a worksheet within the same workbook using the VBA Copy worksheet function.

In this code, we aim to duplicate the worksheet named “Sheet1” within the current workbook. When we execute the code, the Copy method is invoked on the specified worksheet by including the “After” parameter and setting it to “After:=ThisWorkbook.Worksheets(“Sheet1″),” we ensure that the copied worksheet is positioned immediately after the original “Sheet1” within the workbook’s worksheet collection.

The above code creates a replica of the “Sheet1,” which is “Sheet1 (2)” worksheet within the same workbook, maintaining the order of the existing sheets.

What is Excel VBA Copy Worksheet?

VBA provides the “Copy” method to duplicate worksheets in Excel, allowing for the automation of repetitive tasks.
Copying a worksheet can be done within the same workbook or across different workbooks.
Various actions can be performed after copying a worksheet, such as renaming, saving as a different file format, or pasting values.
It’s important to note that when copying worksheets in VBA, you should consider that the worksheet names should be unique within a workbook. If you attempt to copy a worksheet and give it a name that already exists, it will result in an error. Ensure that you either rename the copied worksheet or choose a unique name for it.

Worksheet.Copy Method in VBA (with Steps)

The VBA Copy Worksheet method is a built-in method in VBA that belongs to the Worksheet object. It is used to create a duplicate of a worksheet. Here are the steps to use the Copy method in VBA:

Step 1: Open the Visual Basic Editor (VBE) in Excel by pressing Alt+F11.

Step 2: Insert a new module by clicking “Insert” in the menu bar and selecting “Module.”

Step 3: Write the VBA code that utilizes the Worksheet in the “module.Copy” method.

For example:

Sub CopyWorksheetExample()
Dim wbTarget As Workbook
Set wbTarget = Workbooks.Add
ThisWorkbook.Worksheets(“Sheet1”).Copy After:=wbTarget.Sheets(wbTarget.Sheets.Count)
End Sub

In the above code, we declare a variable wbTarget to store the new workbook. Using “Workbooks.Add”, we create the new workbook and assign it to wbTarget.

Next, we apply the Copy method to the source worksheet obtained from ThisWorkbook.Worksheets(“Sheet1”). It duplicates the worksheet and places it in the target workbook.

The position of the copied worksheet within the target workbook is specified using the After parameter. Here, we set it to After:=wbTarget.Sheets(wbTarget.Sheets.Count), inserting the copied worksheet after the last sheet in the target workbook.

Step 4: Press F5 or click the “Run” button to execute the code.

When you run the code, the “Worksheet.Copy” method is invoked, resulting in the duplication of the “Sheet1” worksheet from the source workbook (current workbook) to a new workbook (wbTarget). The copied worksheet is positioned after the last sheet in the target workbook.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

Examples of Copy Worksheet in VBA

Example #1 – Copy Worksheet to Another Workbook

In this example, the worksheet named “Sheet1” is copied from the source workbook (the workbook containing the VBA code) to a new workbook created with “Workbooks.Add.”

Step 1: First, create a new module following the abovementioned steps. Then, we declare the subroutine called “CopyToAnotherWorkbook.”

Step 2: Now, we declare variables: “Dim wbSource As Workbook, wbTarget As Workbook.”

This line declares two Workbook variables: “wbSource” and “wbTarget.” Variables are used to store and manipulate data within VBA code.

Step 3: In the following line, we set the “wbSource” variable to the workbook that contains the VBA code. “ThisWorkbook” is a predefined keyword that refers to the workbook where the code is written.

Step 4: We create the target workbook: “Set wbTarget = Workbooks.Add.”

This line creates a new workbook using the “Workbooks.Add” method and assigns it to the wbTarget variable. Workbooks refer to the collection of open workbooks and Add creates a new workbook.

Step 5: Now, we provide the code that copies the worksheet named “Sheet1” from the “wbSource” workbook to the wbTarget workbook.

The VBA Copy Worksheet method is used on the source worksheet, and the After parameter is set to insert the copied worksheet after the last sheet in the wbTarget workbook.

Step 6: Save the module and close the VBA. Now press Alt + F8 to open the Macro menu, select “CopyToAnotherWorkbook” and run.

Step 7: Once you execute the code, you will see that the copied worksheet is placed after the last sheet in the target workbook.

Here is the full code:

Sub CopyToAnotherWorkbook()
Dim wbSource As Workbook, wbTarget As Workbook
Set wbSource = ThisWorkbook
Set wbTarget = Workbooks.Add
wbSource.Worksheets(“Sheet1”).Copy After:=wbTarget.Sheets(wbTarget.Sheets.Count)
End Sub

Example #2 – Copy Worksheet and Rename

The following VBA Copy Worksheet example explains how to copy the worksheet “Sheet1” within the same workbook and renames the copied sheet as “Copy of Sheet1”.

Step 1: In the new module, start by declaring the subroutine, “CopyAndRename().”

Step 2: Now provide the code to copy the worksheet named “Sheet1” within the current workbook (“ThisWorkbook”). The Copy method is used on the source worksheet, and the After parameter is set to insert the copied worksheet after the original “Sheet1”.

Step 3: The next line of code renames the newly copied worksheet using the Name property of the ActiveSheet object. Here, it assigns the name “Copy of Sheet1” to the active sheet, which is the newly copied worksheet.

Step 4: Save the module and close the VBA. Now press Alt + F8 to open the Macro menu, select “CopyAndRename()” and Run.

Step 5: Once you execute the code, you will see that the worksheet “Sheet1” within the same workbook is copied and renamed as “Copy of Sheet1”.

Here is the complete code:

Sub CopyAndRename()
ThisWorkbook.Worksheets(“Sheet1”).Copy After:=ThisWorkbook.Worksheets(“Sheet1”)
ActiveSheet.Name = “Copy of Sheet1”
End Sub

Example #3 – Copy Worksheet and Save as CSV

In this example, the worksheet “Sheet1” is copied to a new workbook, and then the new workbook is saved as a CSV file with the SaveAs method.

Step 1: Start a subroutine called “CopyAndSaveAsCSV” in the new module.

Step 2: Next, we declare a Workbook variable “wbNew” and create a new workbook using the “Workbooks.Add” method. The Add method adds a new workbook to the collection of open workbooks and assigns it to the “wbNew” variable.

Step 3: Now, we add a code to copy the worksheet to the new workbook. This line copies the worksheet named “Sheet1” from the source workbook (ThisWorkbook) to the wbNew workbook.

The Copy method is used on the source worksheet, and the After parameter is set to insert the copied worksheet after the last sheet in the wbNew workbook.

Step 4: Finally, we save the new workbook as a CSV file using “wbNew.SaveAs” method and “FileFormat:=xlCSV.”

This line saves the wbNew workbook as a CSV file using the SaveAs method. The file path and name are specified as “C:\Users\Vikram\Desktop\NewWorkbook.csv.” The FileFormat parameter is set to xlCSV, representing the Excel CSV file format.

Step 5: Save the module and close the VBA. Now press Alt + F8 to open the Macro menu, select “CopyAndSaveAsCSV,” and Run.

Step 6: A CSV file is saved in the path provided once you execute the code.

Here is the full code:

Sub CopyAndSaveAsCSV()
Dim wbNew As Workbook
Set wbNew = Workbooks.Add
ThisWorkbook.Worksheets(“Sheet1”).Copy After:=wbNew.Sheets(wbNew.Sheets.Count)
wbNew.SaveAs “C:\Path\To\NewWorkbook.csv”, FileFormat:=xlCSV
End Sub

Example #4 – Copy Worksheet and Paste Values

In this example, the VBA Copy worksheet code copies the entire used range from “Sheet1” to a new worksheet and pastes only the values (removing any formulas or formatting). The PasteSpecial method with the argument `xlPasteValues` is used to paste only the values.

Step 1: The new module starts with creating a subroutine “CopyAndPasteValues().”

Step 2: Now, declare two Worksheet variables: “wsSource” and “wsTarget.” VBA Variables are used to store and manipulate worksheet objects within VBA code.

Step 3: Next, we set the wsSource variable to the worksheet named “Sheet1” within the current workbook (ThisWorkbook). They also create a new worksheet using the Worksheets.Add method and assign it to the wsTarget variable.

Step 4: We provide the code to copy the used range (containing data and formatting) from the wsSource worksheet. The UsedRange property returns the range encompassing all the cells with data or formatting on the worksheet.

Step 5: Next, we provide the code to only paste the values from the clipboard to the wsTarget worksheet. The Range(“A1”) specifies the destination cell where the values should be pasted.

The PasteSpecial method with xlPasteValues argument ensures that only the values are pasted, excluding formulas or formatting.

Step 6: Save the module and close the VBA. Now press Alt + F8 to open the Macro menu, select “CopyAndPasteValues()” and Run.

Step 7: Once you execute the code, you will see that the new worksheet is created and the values are pasted as value only.

Here is the full code:

Sub CopyAndPasteValues()
Dim wsSource As Worksheet, wsTarget As Worksheet
Set wsSource = ThisWorkbook.Worksheets(“Sheet1”)
Set wsTarget = ThisWorkbook.Worksheets.Add
wsSource.UsedRange.Copy
wsTarget.Range(“A1”).PasteSpecial xlPasteValues
End Sub

Important Things To Note

When copying worksheets, it’s essential to specify the destination where the copied sheet should be placed. It can be done using the After parameter in the Copy method, which determines the sheet after which the copy should be inserted.
If you want to VBA Copy Worksheet to another workbook, create a new workbook using the “Workbooks.Add” method and assign it to a variable. Then you can use that variable to reference the new workbook when copying the worksheet.
VBA Copy Worksheet and rename can be done by assigning a new name to the “Name” property of the copied sheet using the “ActiveSheet.Name” property.
To VBA Copy Worksheet and save it as a CSV file, use the “SaveAs” method with the appropriate file path and specify the “FileFormat” argument as “xlCSV” to save it in CSV format.
To VBA Copy Worksheet and paste values, use the “PasteSpecial” method with the argument “xlPasteValues.” It ensures that only the values are pasted, excluding any formulas or formatting.

Frequently Asked Questions (FAQs)

1. Why can’t I copy a worksheet using VBA to a new workbook?

Copying a worksheet to a new workbook requires creating a new workbook using the “Workbooks.Add” method. Ensure you have included this step in your VBA code before copying the worksheet.

2. How can I copy a worksheet from another workbook without opening it?

To copy a worksheet from another workbook without opening it, you can use the Workbooks.Open method with the ReadOnly parameter set to True. It allows you to open the workbook in read-only mode, copy the desired worksheet, and then close the workbook without making any changes.

Here’s an example:

Sub CopyFromClosedWorkbook()
Dim wbSource As Workbook
Set wbSource = Workbooks.Open(“C:\Path\To\SourceWorkbook.xlsx”, ReadOnly:=True)
wbSource.Worksheets(“Sheet1”).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
wbSource.Close SaveChanges:=False
End Sub

In this example, “Sheet1” from the closed workbook “SourceWorkbook.xlsx” is copied to the current workbook without opening the source workbook in an editable mode.

3. How can I copy a worksheet to a new workbook and save it?

To copy a worksheet to a new workbook and save it, you can use the SaveAs method after copying the worksheet. Here’s an example:

Sub CopyAndSaveAsWorkbook()
Dim wbNew As Workbook
Set wbNew = Workbooks.Add
ThisWorkbook.Worksheets(“Sheet1”).Copy After:=wbNew.Sheets(wbNew.Sheets.Count)
wbNew.SaveAs “C:\Path\To\NewWorkbook.xlsx”
wbNew.Close SaveChanges:=False
End Sub

In this example, the worksheet “Sheet1” is copied to a new workbook, and then the new workbook is saved with the desired file path and name using the SaveAs method. Finally, the new workbook is closed without saving any additional changes.

VBA Copy Worksheet

What is Excel VBA Copy Worksheet?

Table of contents

Key Takeaways

Worksheet.Copy Method in VBA (with Steps)