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
Note:
- Here, I declared the Sub procedure Copy_Before_AnySheet__inSameWorkbook.
- I’ve given the sheet name Dataset that 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. 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
Note:
- Here, I declared the Sub procedure Copy_After_AnySheet__inSameWorkbook.
- I’ve given the sheet name Dataset that 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. 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
Note:
- Here, I declared the Sub procedure Copy_After_LastSheet__inSameWorkbook.
- I’ve used the sheet name Dataset that 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.
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
Note:
- Here, I declared the Sub procedure Copy_And_RenameSheet__inSameWorkbook.
- I’ve given the sheet name Dataset that 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 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
Note:
- Here, I declared the Sub procedure Copy_RenameSheet__Based_Cell_Value.
- I’ve given the sheet name Dataset that 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.
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
Note:
- Here, I declared the Sub procedure Copy_Sheet_Multiple_Time where n and i are Integer type variables.
- In n kept 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 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
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 New Workbook.
- 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
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 workbooks New Workbook.xlsm.
- Then, I declared the location using Before:= to place the copied sheet before Sheets (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 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
Note:
- Here, I declared the Public Sub procedure Copy_SpecificSheet_To_NewWorkbook.
- I’ve given the sheet name Dataset that 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. 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
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
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 have given the sheet name PersonalInfo that I want to copy and also given the workbook name VBA Copy Sheet in Excel.xlsm where I want 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. 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.
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