VBA Copy Sheet in Excel (13 Quick Ways)

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 the ways of VBA copy sheet in Excel.

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. These columns are the Name, Email, and Address.

Sample Dataset

Download Workbook to Practice

13 Ways to VBA Copy Sheet in Excel

1. Copy a Sheet Within 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,
First, open the Developer tab >> select Visual Basic
There is a shortcut ALT + F11 to open the Visual Basic

Using VBA to Copy a Sheet Within Same Workbook Before Any Sheet

Next, it will open a new window named Microsoft Visual Basic for Applications.
From there, open Insert >> select Module

A Module will open then type the following code in the opened Module.

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

Using VBA to Copy a Sheet Within Same Workbook Before Any Sheet

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 declared the location Before:=Sheets(“Dataset”) to place the copied sheet.
Here, it will copy the sheet Before the selected sheet Dataset.

Finally, Save the code and go back to the worksheet.
Next, open the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

Using VBA to Copy a Sheet Within Same Workbook Before Any Sheet

Now, from the Macro name select the Copy_Before_AnySheet__inSameWorkbook also select the workbook within Macros in.
Finally, Run the selected Macro.

Hence, it will place the Copied sheet Before the Dataset sheet.

2. Copy a Sheet Within Same Workbook After Any Sheet

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.

To start the procedure,
First, open the Developer tab >> select Visual Basic

Using VBA to Copy a Sheet Within Same Workbook After Any Sheet

Next, it will open a new window of Microsoft Visual Basic for Applications.
From there, open Insert >> select Module

A Module will open then type the following code in the opened Module.

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

Using VBA to Copy a Sheet Within Same Workbook After Any Sheet

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 declared the location After:=Sheets(“Dataset”) to place the copied sheet.
Here, it will copy the sheet After the selected sheet Dataset.

Finally, Save the code and go back to the worksheet.
Then, open the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

Using VBA to Copy a Sheet Within Same Workbook After Any Sheet

Now, from the Macro name select the Copy_After_AnySheet__inSameWorkbook also select the workbook within Macros in.
Finally, Run the selected Macro.

Hence, it will place the Copied sheet After the Dataset sheet.

3. Copy a Sheet Within Same Workbook After the Last 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.

Let’s start the procedure,
First, open the Developer tab >> select Visual Basic

Using VBA to Copy a Sheet Within Same Workbook After the Last Sheet

Next, it will open a new window of Microsoft Visual Basic for Applications.
From there, open Insert >> select Module

A Module will open then type the following code in the opened Module.

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

Using VBA to Copy a Sheet Within Same Workbook After the Last Sheet

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 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.

Finally, Save the code and go back to the worksheet.
After that, open the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

Using VBA to Copy a Sheet Within Same Workbook After the Last Sheet

Now, from the Macro name select the Copy_After_LastSheet__inSameWorkbook also select the workbook within Macros in.
Finally, Run the selected Macro.

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 a Sheet and Rename

To Copy a sheet in an efficient way you also can Rename the copied sheet using the VBA.

Let’s start the procedure,
First, open the Developer tab >> select Visual Basic

Using VBA to Copy a Sheet and Rename

Next, it will open a new window of Microsoft Visual Basic for Applications.
From there, open Insert >> select Module

A Module will open 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

Using VBA to Copy a Sheet and Rename

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 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.
➤I used the name RenamedSheet

In the end, Save the code and go back to the worksheet.
After that, open the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

Using VBA to Copy a Sheet and Rename

Now, from the Macro name select the Copy_And_RenameSheet__inSameWorkbook also select the workbook within Macros in.
Finally, Run the selected Macro.

Hence, it will Rename the sheet and will place the Copied sheet After all the sheets.

5. Copy a Sheet and Rename Based On Cell Value

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

To start the procedure,
First, open the Developer tab >> select Visual Basic

Using VBA to Copy a Sheet and Rename Based On Cell Value

Next, it will open a new window of Microsoft Visual Basic for Applications.
From there, open Insert >> select Module

A Module will open 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

Using VBA to Copy a Sheet and Rename Based On Cell Value

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 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.

Finally, Save the code and go back to the worksheet.
Next, open the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

Using VBA to Copy a Sheet and Rename Based On Cell Value

Now, from the Macro name select the Copy_RenameSheet__Based_Cell_Value also select the workbook within Macros in.
Finally, Run the selected Macro.

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 a Sheet Multiple Time

There is also a way to Copy a sheet multiple times based on your need. You can use VBA Copy to do that.

To begin the procedure,
First, open the Developer tab >> select Visual Basic

Using VBA to Copy a Sheet Multiple Time

Next, it will open a new window of Microsoft Visual Basic for Applications.
From there, open Insert >> select Module

A Module will open 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

Using VBA to Copy a Sheet Multiple Time

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 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.

Now, Save the code and go back to the worksheet.
Then, open the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

Using VBA to Copy a Sheet Multiple Time

Now, from the Macro name select the Copy_Sheet_Multiple_Time also select the workbook within Macros in.
Then, Run the selected Macro.

➤Again another dialog box will pop up.

Then, 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.

Using VBA to Copy a Sheet Multiple Time

7. Copy a Multiple Sheet in 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.

Using VBA to Copy a Multiple Sheet in New Workbook

To begin with,
First, open the Developer tab >> select Visual Basic

Using VBA to Copy a Multiple Sheet in New Workbook

Next, it will open a new window of Microsoft Visual Basic for Applications.
From there, open Insert >> select Module

A Module will open then type the following code in the opened Module.

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

Using VBA to Copy a Multiple Sheet in New Workbook

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.

Finally, Save the code and go back to the worksheet.
After that, open the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

Using VBA to Copy a Multiple Sheet in New Workbook

Now, from the Macro name select the Copy_Multiple_Sheets_NewWorkbook also select the workbook within Macros in.
Finally, Run the selected Macro.

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.

Let’s start the procedure,
First, open the Developer tab >> select Visual Basic

Using VBA to Copy Worksheet to Another Selected Workbook

Next, it will open a new window of Microsoft Visual Basic for Applications.
From there, open Insert >> select Module

A Module will open 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

Using VBA to Copy Worksheet to Another Selected Workbook

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 the selected workbooks New Workbook.xlsm.
Then declared the location using Before:= to place the copied sheet before Sheets (1).

Finally, Save the code and go back to the worksheet.
Next, open the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

Using VBA to Copy Worksheet to Another Selected Workbook

Now, from the Macro name select the Copy_Sheet__To_NewWorkbook also select the workbook within Macros in.
Finally, Run the selected Macro.

As a result, it will place the Copied sheet Before sheet1 in the selected workbook.

9. Copy a 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.

Let’s start the procedure,
First, open the Developer tab >> select Visual Basic

Using VBA to Copy a Specific Sheet To New Workbook

Next, it will open a new window of Microsoft Visual Basic for Applications.
From there, open Insert >> select Module

A Module will open then type the following code in the opened Module.

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

Using VBA to Copy a Specific Sheet To New Workbook

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 in a new workbook.

Finally, Save the code and go back to the worksheet.
After that, open the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

Using VBA to Copy a Specific Sheet To New Workbook

Now, from the Macro name select the Copy_SpecificSheet_To_NewWorkbook also select the workbook within Macros in.
Finally, Run the selected Macro.

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.

First, open the Developer tab >> select Visual Basic

Using VBA to Copy Active Sheet to Existing Workbook

Next, it will open a new window of Microsoft Visual Basic for Applications.
From there, open Insert >> select Module

A Module will open 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

Using VBA to Copy Active Sheet to Existing Workbook

Here, I declared the Sub procedure Copy_Active_SheetToExistingWorkbook
I’ve given the sheet name Dataset also the workbook name where the sheet exists. And used the Copy method to copy.
Then declared the location After:= and used the Count method to place the copied sheet after all the existing sheets.

Now, Save the code and go back to the worksheet.
Next, open the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

Using VBA to Copy Active Sheet to Existing Workbook

Now, from the Macro name select the Copy_Active_SheetToExistingWorkbook also select the workbook within Macros in.
Finally, Run the selected Macro.

Hence, it will place the Copied sheet After all the existing sheets of the existing workbook.

11. Copy Worksheet to Closed Workbook

Depending on your choice you can copy a dataset to a closed workbook without opening the workbook.

To perform this task,
First, open the Developer tab >> select Visual Basic

Using VBA to Copy Worksheet to Closed Workbook

Next, it will open a new window of Microsoft Visual Basic for Applications.
From there, open Insert >> select Module

A Module will open 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

Using VBA to Copy Worksheet to Closed Workbook

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 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.

Finally, Save the code and go back to the worksheet.
Next, open the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

Using VBA to Copy Worksheet to Closed Workbook

Now, from the Macro name select the Copy_Sheet_To_Closed_WorkBook also select the workbook within Macros in.
Finally, Run the selected Macro.

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.

Let’s start the procedure,
First, open the Developer tab >> select Visual Basic

Using VBA to Copy Sheet From Closed Workbook

Next, it will open a new window of Microsoft Visual Basic for Applications.
From there, open Insert >> select Module

A Module will open 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

Using VBA to Copy Sheet From Closed Workbook

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 given the sheet name PersonalInfo that I want to copy also given the workbook name VBA Copy Sheet in Excel.xlsm where I want to place the copied sheet.

Finally, Save the code and go back to the worksheet.
Next, open the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

Using VBA to Copy Sheet From Closed Workbook

Now, from the Macro name select the Copy_Sheet_From_Closed_WorkBook also select the workbook within Macros in.
Finally, Run the selected Macro.

Now, it will copy the selected sheet from a closed workbook to the selected workbook.

13. Copy a 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.

To perform this task,
First, open the Developer tab >> select Visual Basic

Using VBA to Copy a Sheet to Another Blank Workbook and Save

Next, it will open a new window of Microsoft Visual Basic for Applications.
From there, open Insert >> select Module

A Module will open 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

Using VBA to Copy a Sheet to Another Blank Workbook and Save

Here, I declared the Sub procedure Copy_Sheet_To_NewWorkbook_Save
First, 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 used the SaveAs method to save the copied sheets and used the Close method to close the saved workbook.

In the end, Save the code and go back to the worksheet.
Next, open the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

Using VBA to Copy a Sheet to Another Blank Workbook and Save

Now, from the Macro name select the Copy_Sheet_To_NewWorkbook_Save also select the workbook within Macros in.
Finally, Run the selected Macro.

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.

Using VBA to Copy a Sheet to Another Blank Workbook and Save

Practice section

I’ve provided a practice sheet in the workbook to practice these explained ways.

Conclusion

In this article, I’ve explained 13 different types of easy and quick ways of VBA copy sheet 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.


Related Articles

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo