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

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview of VBA Copy Sheet Within Same Workbook Before Any Sheet

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.

Sample Dataset

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.

insert vba 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

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

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.

copy sheet before existing dataset result


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

VBA code to Copy Sheet Within Same Workbook After Any Sheet

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.

Copy Sheet Within Same Workbook After Any 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

VBA code to Copy Sheet Within Same Workbook After Last Sheet

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.

Copy Sheet Within Same Workbook After Last Sheet final output


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

Duplicate Sheet and Rename

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.

result of Copy Sheet and Rename


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

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

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.

Copy Sheet and Rename Based On Cell Value result

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

duplicate a Sheet Multiple Times

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.

excel vba dialog box

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

Copy Sheet Multiple Time final result


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.

Copy Multiple Sheet in New Workbook dataset

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

Using VBA code to Copy a Multiple Sheet in New Workbook

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.

output of Copying Sheet Multiple Times with vba

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

Using VBA code to Copy Worksheet to Another Selected Workbook

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.

Copy Worksheet to Another Selected Workbook final result


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

Copy a Specific Sheet To a New Workbook

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.

Copy Specific Sheet to 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

Using VBA code to Copy Active Sheet to Existing Workbook

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.

Copy Active Sheet to Existing Workbook output


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

Using VBA code to Copy Worksheet to Closed Workbook

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.

Copy Worksheet to Closed Workbook final result


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

Copy Sheet From Closed Workbook code

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.

Duplicate a worksheet From Closed Workbook final result


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

VBA code

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.

Copy Sheet to Another Blank Workbook and Save result

Read More: Excel Copy Worksheet to Another Workbook Without Reference


Excel VBA Copy Sheet: Knowledge Hub


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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shamima Sultana
Shamima Sultana

Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 2 years. She has written over 75+ articles and reviewed 1000+ articles for ExcelDemy. She has also led several teams with Excel VBA and Content Development works. Currently, she is working as the Project Manager and oversees the day-to-day work, leads the services team, allocates resources to the right area, etc. Her work and learning interests vary from Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo