How to Copy Sheet with VBA in Excel: 13 Quick Methods

Method 1 – Using Excel VBA to Copy a Worksheet in the Same Workbook (Before Any Sheet)

Steps:

Press ALT + F11 to open the Microsoft Visual Basic for Applications window.
Open Insert >> select Module.

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:

Declared the Sub procedure Copy_Before_AnySheet__inSameWorkbook.
We’ve given the sheet name “Dataset” which I want to copy using the Copy method.
Declared the location Before:=Sheets(“Dataset”) to place the copied sheet.
Copy the sheet before the selected sheet “Dataset”.

Hit the F5 key to run the code.
Place the copied sheet before the “Dataset” sheet.

Method 2 – Applying VBA to Make a Copy of the Worksheet in the Same Workbook (After Any Sheet)

Steps:

Open a new module following the steps mentioned in method 1.
Type the following code in the opened Module.

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

Note:

Declared the Sub procedure Copy_After_AnySheet__inSameWorkbook.
We’ve given the sheet name “Dataset” which I want to copy using the Copy method.
Declared the location After:=Sheets(“Dataset”) to place the copied sheet.
Copy the sheet after the selected sheet “Dataset”.

Hit the F5 key to run the code.
Place the copied sheet after the “Dataset” sheet.

Method 3 – Copying Sheet in Same Workbook After Utmost Sheet

Steps:

Open a new module following the steps mentioned in method 1.
Type the following code in the opened Module.

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

Note:

Declared the Sub procedure Copy_After_LastSheet__inSameWorkbook.
We’ve used the sheet name “Dataset” which I want to copy using the Copy method.
Declared the location After:=Sheets(Sheets.Count) to place the copied sheet.
Sheets.Count will count all the existing sheets and place the copied sheet after all the existing sheets.

Hit the F5 key to run the code.
Place the copied sheet after all the existing sheets.

Method 4 – Copying and Renaming Worksheet with Excel VBA

Steps:

Open a new module following the steps mentioned in method 1.
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:

Declared the Sub procedure Copy_And_RenameSheet__inSameWorkbook.
We’ve given the sheet name “Dataset” which I want to copy using the Copy method.
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.
Used the name “RenamedSheet”.

Hit the F5 key to run the code.
Rename the sheet and place the copied sheet after all the sheets.

Method 5 – Copy and Rename Sheet According to Cell Value

Steps:

Open a new module following the steps mentioned in method 1.
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:

Declared the Sub procedure Copy_RenameSheet__Based_Cell_Value.
We’ve given the sheet name “Dataset” which I want to copy using the Copy method.
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.
Used the Range(“B3”).Value.
Copy the sheet After all the existing sheets and Rename it based on the selected cell value.

Hit the F5 key to run the code.
Place the Copied sheet After all the existing sheets and will Rename the sheet as a Name depending on the selected cell value.

Method 6 – Copying Sheet Multiple Times with VBA in Excel

Steps:

Open a new module following the steps mentioned in Method 1.
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:

Declared the Sub procedure Copy_Sheet_Multiple_Time where n and i are Integer type variables.
Keep the InputBox message where it will take the number of copies you want.
Used a FOR loop within the IF statement.
Selected the Name sheet as an ActiveSheet then used the Copy method.
Mention the location to place your copied sheet.
Used the After and Count method to place the copied sheet after all the existing sheets.

Hit the F5 key to run the code.
Another dialog box will pop up.
Put the number to get the number of copies you want. I wanted two copies, so I gave 2.
Click OK.

Create 2 copies of the Name sheet After all the existing sheets.

Method 7 – Copy Multiple Sheets into New Workbook with Excel VBA

Steps:

Open a new module following the steps mentioned in Method 1.
Type the following code in the opened Module.

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

Note:

Declared the Public Sub procedure Copy_Multiple_Sheets_NewWorkbook.
Used ActiveWindow.SelectedSheets.Copy command.
Copy the selected sheets of the ActiveWindow to the New Workbook.

Hit the F5 key to run the code.
Copy the selected sheets into another new workbook.

Method 8 – Copy the Worksheet to Another Selected Workbook

Steps:

Open a new module following the steps mentioned in Method 1.
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:

Declared the Sub procedure Copy_Sheet__To_NewWorkbook.
We’ve given the sheet name “Dataset” that I want to copy using the Copy method to the mentioned selected workbook “New Workbook”.
Declared the location using Before:= to place the copied sheet before “Sheet(1)”.

Hit the F5 key to run the code.
Place the Copied sheet Before “Sheet1” in the selected workbook.

Method 9 – Copy Specific Sheets to New Workbook with VBA Code

Steps:

Open a new module following the steps mentioned in Method 1.
Type the following code in the opened Module.

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

Note:

We declared the Public Sub procedure Copy_SpecificSheet_To_NewWorkbook.
We’ve given the sheet name “Dataset” which I want to copy using the Copy method.
Copy the sheet into a new workbook.

Hit the F5 key to run the code.
Copy the selected sheet to a new workbook.

Method 10 – Copy Active Sheet to Existing Workbook

Steps:

Open a new module following the steps mentioned in Method 1.
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:

We declared the Sub procedure Copy_Active_SheetToExistingWorkbook.
We’ve given the sheet name “Dataset” as I want to copy this active sheet and also the workbook name where the sheet exists.
We used the Copy method to copy.
We declared the location After:= and used the Count method to place the copied sheet after all the existing sheets.

Hit the F5 key to run the code.
Place the Copied sheet After all the existing sheets of the existing workbook.

Method 11 – Using VBA to Copy Sheet into a Closed Workbook

Steps:

Open a new module following the steps mentioned in Method 1.
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:

We declared the Sub procedure Copy_Sheet_To_Closed_WorkBook.
We specified the path of the closed workbook where I want to place the copied sheet.
We declared the workbook name and the sheet name from where I wanted to copy the data.
Copy the sheet to the closed workbook.

Hit the F5 key to run the code.
Copy the selected sheet to the closed workbook.

Method 12 – Copy Sheet From Closed Workbook with Excel VBA

Steps:

Open a new module following the steps mentioned in Method 1.
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:

We declared the Sub procedure Copy_Sheet_From_Closed_WorkBook.
We’ve specified the path of the closed workbook from where I want to copy any sheet.
We gave the sheet name “PersonalInfo” that I wanted to copy and also gave the workbook name “VBA Copy Sheet in Excel” where to place the copied sheet.

Hit the F5 key to run the code.
Copy the selected sheet from a closed workbook to the selected workbook.

Method 13 – Applying VBA to Copy Sheet to Another Blank Workbook and Save

Steps:

Open a new module following the steps mentioned in Method 1.
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:

We declared the Sub procedure Copy_Sheet_To_NewWorkbook_Save.
We gave the sheet name “Dataset” to copy it into another blank workbook.
We specified the path where I want to save the copied sheet.
We used the SaveAs method to save the copied sheets and the Close method to close the saved workbook.

Hit the F5 key to run the code.
Copy the selected sheet to a new blank workbook and save and close it.
In the specified path, check that the new workbook is created with the copied sheet. See the new workbook with a copied sheet.

Excel VBA Copy Sheet: Knowledge Hub

Download Workbook to Practice

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

VBA Copy Sheet in Excel.xlsm

Get FREE Advanced Excel Exercises with Solutions!

How to Copy Sheet with VBA in Excel: 13 Quick Methods

Method 1 – Using Excel VBA to Copy a Worksheet in the Same Workbook (Before Any Sheet)

Method 2 – Applying VBA to Make a Copy of the Worksheet in the Same Workbook (After Any Sheet)

Method 3 – Copying Sheet in Same Workbook After Utmost Sheet

Method 4 – Copying and Renaming Worksheet with Excel VBA

Method 5 – Copy and Rename Sheet According to Cell Value

Method 6 – Copying Sheet Multiple Times with VBA in Excel

Method 7 – Copy Multiple Sheets into New Workbook with Excel VBA

Method 8 – Copy the Worksheet to Another Selected Workbook

Method 9 – Copy Specific Sheets to New Workbook with VBA Code

Method 10 – Copy Active Sheet to Existing Workbook

Method 11 – Using VBA to Copy Sheet into a Closed Workbook

Method 12 – Copy Sheet From Closed Workbook with Excel VBA

Method 13 – Applying VBA to Copy Sheet to Another Blank Workbook and Save

Excel VBA Copy Sheet: Knowledge Hub

Related Articles

Leave a reply Cancel reply