How to Copy Sheet with VBA in Excel (13 Quick Ways)

Fortunately, there exist various ways to copy a sheet in Excel. You can copy a sheet in the active workbook or into another workbook or a new workbook. In this article, I will explain 13 quick ways to copy sheet with VBA in Excel. Hence, read the below part to learn more and save time.

Here, I have copied a sheet within the same workbook before any sheet using VBA. Read through the rest of the part to learn this along with more methods in detail.

Download Workbook to Practice

You can download the workbook used for the demonstration from the download link below.

13 Suitable Ways to Copy Sheet with VBA in Excel

However, to make the explanation clearer, I’m going to use a sample dataset that represents the personal information of particular persons. The dataset has 3 columns. However, these columns are the Name, Email, and Address. Using this, we will show some suitable ways to copy sheets with VBA in Excel.

1. Copy Sheet in Same Workbook Before Any Sheet

To perform different formulas and functions in the same sheet you may need to copy the sheet. You can Copy a sheet within the same workbook Before any sheet using VBA. Let’s start the procedure. Hence, follow the steps below.

Steps:

• First, press ALT + F11 to Microsoft Visual Basic for Applications window.
• From there, open Insert >> select Module.

• Afterwards, a Module will open, and type the following code in the opened Module.

Sub Copy_Before_AnySheet__inSameWorkbook()
Sheets("Dataset").Copy Before:=Sheets("Dataset")
End Sub

• Finally, hit the F5 key to run the code.
• Hence, it will place the Copied sheet Before the Dataset sheet.

2. Copy Worksheet in Same Workbook After Any Sheet

Furthermore, while performing different formulas and functions in the same sheet you can copy the same sheet also mentioning the place where you want to place your copied sheet. Using VBA you can Copy a sheet within the same workbook after any sheet. Hence, follow the steps below.

Steps:

• Similarly, open a new module following the steps mentioned in Method 1.
• Then, type the following code in the opened Module.

Sub Copy_After_AnySheet__inSameWorkbook()
Sheets("Dataset").Copy After:=Sheets("Dataset")
End Sub

• After that, hit the F5 key to run the code.
• Hence, it will place the Copied sheet After the Dataset sheet.

3. Copy Sheet in Same Workbook After Utmost Sheet

In case you want to keep the copied sheet after the last sheet you also can use the VBA to Copy a sheet in that way. Hence, follow the steps below.

Steps:

• Similarly, open a new module following the steps mentioned in Method 1.
• Then, type the following code in the opened Module.

Sub Copy_After_LastSheet__inSameWorkbook()
Sheets("Dataset").Copy After:=Sheets(Sheets.Count)
End Sub

• After that, hit the F5 key to run the code.
• Hence, it will place the Copied sheet After all the existing sheets.

Related Content: Copy Worksheet to Another Workbook Without Reference in Excel

4. Copy Sheet and Rename

To Copy a sheet in an efficient way you also can Rename the copied sheet using the VBA. Hence, follow the steps below.

Steps:

• Similarly, open a new module following the steps mentioned in Method 1.
• Then, type the following code in the opened Module.

Sub Copy_And_RenameSheet__inSameWorkbook()
Sheets("Dataset").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "RenamedSheet"
End Sub

• After that, hit the F5 key to run the code.
• Hence, it will Rename the sheet and will place the Copied sheet After all the sheets.

5. Copy Sheet and Rename According to Cell Value

The VBA also gives us the opportunity to Rename copied sheets based on the cell values of an existing sheet. Hence, follow the steps below.

Steps:

• Similarly, open a new module following the steps mentioned in Method 1.
• Then, type the following code in the opened Module.

Sub Copy_RenameSheet__Based_Cell_Value()
Sheets("Dataset").Copy After:=Sheets(Sheets.Count)
On Error Resume Next
ActiveSheet.Name = Range("B3").Value
On Error GoTo 0
End Sub

• After that, hit the F5 key to run the code.
• Hence, it will place the Copied sheet After all the existing sheets and will Rename the sheet as a Name depending on the selected cell value.

6. Copy Sheet Multiple Times

There is also a way to Copy a sheet multiple times based on your need. You can use VBA Copy to do that. Hence, follow the steps below.

Steps:

• Similarly, open a new module following the steps mentioned in Method 1.
• Then, type the following code in the opened Module.

Sub Copy_Sheet_Multiple_Time()
Dim n As Integer
Dim i As Integer
On Error Resume Next
n = InputBox("How many copies do you want to make?")
If n > 0 Then
For i = 1 To n
ActiveSheet.Copy After:=ActiveWorkbook.Sheets(Worksheets.Count)
Next
End If
End Sub

• After that, hit the F5 key to run the code.
• Again another dialog box will pop up.
• Now, you can put the number to get the number of copies you want. I want two copies, so I gave 2.
• Finally, click Ok

• Thus, it will create 2 copies of the Name sheet After all the existing sheets.

7. Copy Multiple Sheets into New Workbook

If you want, you can Copy multiple sheets to the new workbook using the VBA Copy method. For that first, you need to select the sheets you want to copy to another workbook. Here, I selected the sheet’s Dataset and Name. Hence, follow the steps below.

Steps:

• Similarly, open a new module following the steps mentioned in Method 1.
• Then, type the following code in the opened Module.

Public Sub Copy_Multiple_Sheets_NewWorkbook()
ActiveWindow.SelectedSheets.Copy
End Sub

• After that, hit the F5 key to run the code.
• Therefore, it will Copy the selected sheets into another new workbook.

8. Copy Worksheet to Another Selected Workbook

Using the VBA Copy sheet, you can copy any sheet to any selected workbook. Hence, follow the steps below.

Steps:

• Similarly, open a new module following the steps mentioned in Method 1.
• Then, type the following code in the opened Module.

Sub Copy_Sheet__To_NewWorkbook()
Sheets("Dataset").Copy Before:=Workbooks("New WorkBook.xlsm").Sheets(1)
End Sub

• After that, hit the F5 key to run the code.
• As a result, it will place the Copied sheet Before sheet1 in the selected workbook.

9. Copy Specific Sheet to New Workbook

If you want, you also can Copy a specific sheet to a new workbook. Here you can use the VBA to copy the sheet. Hence, follow the steps below.

Steps:

• Similarly, open a new module following the steps mentioned in Method 1.
• Then, type the following code in the opened Module.

Public Sub Copy_SpecificSheet_To_NewWorkbook()
Sheets("Dataset").Copy
End Sub

• After that, hit the F5 key to run the code.
• Hence, it will only Copy the selected sheet to a new workbook.

10. Copy Active Sheet to Existing Workbook

In case you want to Copy the active sheet to the existing workbook you will be able to do it using the VBA as well. Let’s explore the procedure. Hence, follow the steps below.

Steps:

• Similarly, open a new module following the steps mentioned in Method 1.
• Then, type the following code in the opened Module.

Sub Copy_Active_SheetToExistingWorkbook()
Dim targetSheet As Workbook
Set targetSheet = Workbooks("VBA Copy Sheet in Excel.xlsm")
Sheets("Dataset").Copy After:=targetSheet.Sheets(targetSheet.Worksheets.Count)
End Sub

• After that, hit the F5 key to run the code.
• Hence, it will place the Copied sheet After all the existing sheets of the existing workbook.

11. VBA to Copy Sheet in Closed Workbook

Depending on your choice you can copy a dataset to a closed workbook without opening the workbook. Hence, follow the steps below.

Steps:

• Similarly, open a new module following the steps mentioned in Method 1.
• Then, type the following code in the opened Module.

Sub Copy_Sheet_To_Closed_WorkBook()
Application.ScreenUpdating = False
Set closedBook = Workbooks.Open("C:\Users\shami\OneDrive\Desktop\SoftEko\VBA Copy Sheet in Excel\Closed Workbook.xlsm")
Workbooks("VBA Copy Sheet in Excel.xlsm").Sheets("Dataset").Copy Before:=closedBook.Sheets(1)
closedBook.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub

• After that, hit the F5 key to run the code.
• Hence, it will Copy the selected sheet to the closed workbook.

12. Copy Sheet From Closed Workbook

When you know what types of datasets your worksheet has, then you can copy those sheets to any workbook without opening the workbook. You can perform this task using the VBA. Hence, follow the steps below.

Steps:

• Similarly, open a new module following the steps mentioned in Method 1.
• Then, type the following code in the opened Module.

Sub Copy_Sheet_From_Closed_WorkBook()
Dim closedBook As Workbook
Application.ScreenUpdating = False
Set closedBook = Workbooks.Open("C:\Users\shami\OneDrive\Desktop\SoftEko\VBA Copy Sheet in Excel\Book2.xlsx")
closedBook.Sheets("PersonalInfo").Copy Before:=Workbooks("VBA Copy Sheet in Excel.xlsm").Sheets(1)
closedBook.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub

• After that, hit the F5 key to run the code.
• Now, it will copy the selected sheet from a closed workbook to the selected workbook.

13. Copy Sheet to Another Blank Workbook and Save

The VBA copy sheets give us so many varieties of copying any sheet. You can copy a sheet to a new blank workbook and also save the workbook without opening it. Hence, follow the steps below.

Steps:

• Similarly, open a new module following the steps mentioned in Method 1.
• Then, type the following code in the opened Module.

Sub Copy_Sheet_To_NewWorkbook_Save()
Worksheets("Dataset").Copy
With ActiveWorkbook
.SaveAs Filename:="C:\Users\shami\OneDrive\Desktop\SoftEko\VBA Copy Sheet in Excel" & "\NewWorkbook.xlsx", _
FileFormat:=xlOpenXMLWorkbook
.Close SaveChanges:=False
End With
End Sub

• After that, hit the F5 key to run the code.
• As a result, it will copy the selected sheet to a new blank workbook and save and close it.
• In the specified path, you can check that the new workbook is created with the copied sheet. Here, you will see the new workbook with a copied sheet.

Excel VBA Copy Sheet: Knowledge Hub

• Copy Worksheet to Another Workbook
• Copy Worksheet to Another Workbook Without Formulas
• Copy Worksheet to Another Workbook Without Opening
• Copy Worksheet to Another Workbook and Rename
• Copy Sheet to End

Conclusion

In this article, I’ve explained 13 different types of easy and quick ways of VBA copy sheets in Excel. These different ways will help you to copy any sheet to the same workbook or another workbook. Last but not least, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.

For more information like this, visit Exceldemy.com.

Related Articles

• How to Copy Excel Sheet to Another Sheet with Same Format