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
- 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.
Related Articles
- How to Copy Excel Sheet to Another Sheet with Same Format
- How to Copy Excel Sheet into Word
- [Fixed!] Move or Copy Sheet Not Working in Excel
- How to Copy a Sheet to Another Sheet in Excel
- How to Copy Sheet to Another Workbook with Excel Formulas
