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 sheets 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. 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. Using Excel VBA to Copy a Worksheet in the 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 open the Microsoft Visual Basic for Applications window.
- From there, open Insert >> select Module.
- Afterwards, a Module will open. Type the following code in the opened Module.
Sub Copy_Before_AnySheet__inSameWorkbook()
Sheets("Dataset").Copy Before:=Sheets("Dataset")
End Sub
Note:
- Here, I declared the Sub procedure Copy_Before_AnySheet__inSameWorkbook.
- I’ve given the sheet name “Dataset” which I want to copy using the Copy method.
- Then, I declared the location Before:=Sheets(“Dataset”) to place the copied sheet.
- Here, it will copy the sheet before the selected sheet “Dataset”.
- Finally, hit the F5 key to run the code.
- Hence, it will place the copied sheet before the “Dataset” sheet.
2. Applying VBA to Make a Copy of the Worksheet in the 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
Note:
- Here, I declared the Sub procedure Copy_After_AnySheet__inSameWorkbook.
- I’ve given the sheet name “Dataset” which I want to copy using the Copy method.
- Then, I declared the location After:=Sheets(“Dataset”) to place the copied sheet.
- Here, it will copy the sheet after the selected sheet “Dataset”.
- After that, hit the F5 key to run the code.
- Hence, it will place the copied sheet after the “Dataset” sheet.
3. Copying 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
Note:
- Here, I declared the Sub procedure Copy_After_LastSheet__inSameWorkbook.
- I’ve used the sheet name “Dataset” which I want to copy using the Copy method.
- Then, I declared the location After:=Sheets(Sheets.Count) to place the copied sheet.
- Here, Sheets.Count will count all the existing sheets and place the copied sheet after all the existing sheets.
- After that, hit the F5 key to run the code.
- Hence, it will place the copied sheet after all the existing sheets.
4. Copying and Renaming Worksheet with Excel VBA
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
Note:
- Here, I declared the Sub procedure Copy_And_RenameSheet__inSameWorkbook.
- I’ve given the sheet name “Dataset” which I want to copy using the Copy method.
- Then, I declared the location After:=Sheets(“Dataset”) to place the copied sheet after all existing sheets.
- In the ActiveSheet.Name you can write any name to rename your sheet.
- Here, I used the name “RenamedSheet”.
- 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 and Rename Sheet 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
Note:
- Here, I declared the Sub procedure Copy_RenameSheet__Based_Cell_Value.
- I’ve given the sheet name “Dataset” which I want to copy using the Copy method.
- Then, I declared the locationAfter:=Sheets(“Dataset”) to place the copied sheet After all the existing sheets.
- In ActiveSheet.Name you can use any range Value to Rename your sheet based on the value.
- I used the Range(“B3”).Value.
- Here, it will copy the sheet After all the existing sheets and will Rename it based on the selected cell value.
- 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.
Read More: Excel VBA to Copy and Rename a Worksheet Multiple Times
6. Copying Sheet Multiple Times with VBA in Excel
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
Note:
- Here, I declared the Sub procedure Copy_Sheet_Multiple_Time where n and i are Integer type variables.
- In n keep the InputBox message where it will take the number of copies you want.
- Then, I used a FOR loop within the IF statement.
- I’ve selected the Name sheet as an ActiveSheet then used the Copy method.
- If you want, you also can mention the location to place your copied sheet.
- Here, I used the After and Count method to place the copied sheet after all the existing sheets.
- 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 wanted 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 with Excel VBA
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
Note:
- Here, I declared the Public Sub procedure Copy_Multiple_Sheets_NewWorkbook.
- Then, I used ActiveWindow.SelectedSheets.Copy command.
- Here, it will copy the selected sheets of the ActiveWindow to the New Workbook.
- After that, hit the F5 key to run the code.
- Therefore, it will Copy the selected sheets into another new workbook.
Read More: Excel VBA to Copy Multiple Sheets to New Workbook
8. Copy the 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
Note:
- Here, I declared the Sub procedure Copy_Sheet__To_NewWorkbook.
- I’ve given the sheet name “Dataset” that I want to copy using the Copy method to the mentioned selected workbook “New Workbook”.
- Then, I declared the location using Before:= to place the copied sheet before “Sheet(1)”.
- 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 Sheets to New Workbook with VBA Code
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
Note:
- Here, I declared the Public Sub procedure Copy_SpecificSheet_To_NewWorkbook.
- I’ve given the sheet name “Dataset” which I want to copy using the Copy method.
- Here, it will simply copy the sheet into a new workbook.
- 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
Note:
- Here, I declared the Sub procedure Copy_Active_SheetToExistingWorkbook.
- I’ve given the sheet name “Dataset” as I want to copy this active sheet and also the workbook name where the sheet exists.
- Next, I used the Copy method to copy.
- Then, I declared the location After:= and used the Count method to place the copied sheet after all the existing sheets.
- 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. Using VBA to Copy Sheet into a 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
Note:
- Here, I declared the Sub procedure Copy_Sheet_To_Closed_WorkBook.
- I’ve specified the path of the closed workbook where I want to place the copied sheet.
- Then, I declared the workbook name and the sheet name from where I wanted to copy the data.
- Here, it will copy the sheet to the closed workbook.
- 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 with Excel VBA
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
Note:
- Here, I declared the Sub procedure Copy_Sheet_From_Closed_WorkBook.
- I’ve specified the path of the closed workbook from where I want to copy any sheet.
- Then, I gave the sheet name “PersonalInfo” that I wanted to copy and also gave the workbook name “VBA Copy Sheet in Excel” where I wanted to place the copied sheet.
- 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. Applying VBA to 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
Note:
- Here, I declared the Sub procedure Copy_Sheet_To_NewWorkbook_Save.
- First, I have given the sheet name “Dataset” to copy it into another blank workbook.
- Next, I’ve specified the path where I want to save the copied sheet.
- Then, I used the SaveAs method to save the copied sheets and used the Close method to close the saved workbook.
- 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.
Read More: Excel Copy Worksheet to Another Workbook Without Reference
Excel VBA Copy Sheet: Knowledge Hub
- How to Copy Worksheet to Another Workbook Using VBA
- VBA to Copy Excel Worksheet to Another Workbook without Formulas
- Excel VBA: Copy Worksheet to Another Workbook Without Opening
- VBA to Copy Worksheet to Another Workbook and Rename
- How to Copy Sheet to End in Excel Using VBA
Download Workbook to Practice
You can download the workbook used for the demonstration from the download link below.
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.
