We have a workbook with two worksheets, NewYork and Boston. The name of the workbook is “Excel VBA Copy Sheet to End”. We have another workbook named “Test-Workbook” with a worksheet named Los_Angeles. We’ll copy these sheets between the workbooks.
⧠Opening Microsoft Visual Basic and Inserting Code in the Module in Excel
Opening Microsoft Visual Basic: There are three main ways to open the Microsoft Visual Basic window.
- Using Keyboard Shortcuts
 Press Alt + F11.
- Using Developer Tab
Go to the Developer tab and select Visual Basic.
- Using Worksheet Tab
Right-click on a worksheet name and choose View Code (from the context menu).
Inserting a Module in Microsoft Visual Basic: There are 2 ways to insert a Module in Microsoft Visual Basic window:
- After opening the Microsoft Visual Basic window, select a Worksheet from the list, right-click on it, then select Insert and choose Module.
- Select Insert from the toolbar and choose Module.
Excel VBA Copy Sheet to the End: 6 Easy Ways
Method 1 – Using Excel VBA to Copy a Specific Sheet to the End in Same Workbook
We want to copy a specific sheet (i.e., NewYork) to the end of the current workbook.
Step 1:
- Insert a VBA module.
- In the Module, paste the following code.
Sub Copy_Sheet_inSameWorkbook()
Sheets("NewYork").Copy After:=Sheets(Sheets.Count)
End Sub
1 – starts the macro procedure by declaring the Sub name. You can assign any name to the code.
2 – copies the “NewYork” sheet in the same workbook using VBA Sheets.Copy.
Step 2:
- Press F5 to run the Macro. After returning to the workbook, the NewYork worksheet will be copied to the end.
Method 2 – Using Excel VBA to Copy the Active Sheet to the End in Same Workbook
In the “Excel VBA Copy Sheet to End” workbook, we want to copy the active worksheet (i.e., “Boston”) to the end of the same workbook.
Step 1:
- Insert a Module following the instruction section.
- Use this macro code in the Module.
Sub Copy_ActiveSheet_ToEnd()
ActiveSheet.Copy After:=Sheets(Sheets.Count)
End Sub
1 – begins the macro code declaring the VBA Macro Code’s Sub name.
2 – the macro copies the active sheet using the VBA activesheet.copy function and places it at the end of the workbook.
Step 2:
- Hit F5 to run the macro and it inserts the active sheet to the end of the workbook as depicted in the following image.
Method 3 – Copy a Specific Sheet to the End from an Open Workbook to Another Using Excel VBA
We have two workbooks. One is the main workbook (i.e., “Excel VBA Copy Sheet to End”) and the other is the test workbook (i.e., “Test-Workbook”). Both of the workbooks are open. We want to copy a specific sheet (i.e., “NewYork”) from our main workbook to the test workbook.
Step 1:
- Open Microsoft Visual Basic and insert the Module section.
- Use the following macro code in the Module.
Sub Copy_SPSheet_ToEnd()
Sheets("NewYork").Copy After:=Workbooks("Test-Workbook.xlsm").Sheets(Workbooks("Test-Workbook.xlsm").Sheets.Count)
End Sub
1 – initiates the macro procedure declaring the Sub name.
2 – VBA Sheets.Copy copies of the specific sheet at the end of the test workbook (i.e., “Test-Workbook”).
Step 2:
- Press F5 to run the macro and it paste the “NewYork” worksheet at the end of the test workbook.
Read More: How to Copy Worksheet to Another Workbook Using VBA
Method 4 – VBA to Copy a Sheet to the End of a Closed Workbook
We want the “Los_Angeles” sheets at the end of the test workbook. We closed the test workbook.
Step 1:
- Open a new Excel file or the VBA to Copy workbook.
- In a Module, insert the following macro code.
Sub Copy_SheetToEnd_ClosedWorkbook()
Dim mrfbook As Workbook
Application.ScreenUpdating = False
Set mrfbook = Workbooks.Open _
("C:\Users\maruf\Desktop\Softeko\Rough\Test-Workbook.xlsm")
Workbooks("Test-Workbook.xlsm").Sheets("Los_Angeles").Copy After:=mrfbook.Sheets(Sheets.Count)
mrfbook.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub
1 – forwards the macro by setting the Sub name.
2 – declares the variable as a Workbook.
3 – assigns the object variable as False (in order not to update) and mrfbook to a source workbook.
4 – the Workbook function takes “Test-Workbook” as the copy of a workbook. And Sheets.Copy copies the “Los_Angeles” worksheet at its end.
5 – closes the “Test-Workbook” and update the workbook.
Step 2:
- Press F5. The macro copies the “Los_Angeles” sheet and places it at the end of the workbook.
Method 5 – Copy a Sheet from Another Workbook to the End Without Opening Using Excel VBA
We have to copy a worksheet from another workbook without opening it. We closed the test workbook. We want to copy the test workbook’s “Los_Angeles” sheet in the main workbook and place it at the end.
Step 1:
- Use the below VBA Macro Code in the Microsoft Visual Basic Module.
Public Sub Copy_Sheet_FromClosedWorkbook()
Dim mrfBook As Workbook
Application.ScreenUpdating = False
Set mrfBook = Workbooks.Open("C:\Users\maruf\Desktop\Softeko\Rough\Test-Workbook.xlsm")
mrfBook.Sheets("Los_Angeles").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
mrfBook.Close
Application.ScreenUpdating = True
End Sub
1 – begins the macro by setting the Sub name.
2 – declares the variable as Workbook.
3 – assigns the object variable and Workbook to a source workbook (i.e., mrfbook).
4 – copies a specific sheet from the source workbook then place the worksheet at the end of the main workbook. VBA Sheets.Copy After function does the job.
5 – assigns the object variable to update the workbook.
Step 2:
- Push the F5 key to run the Macro.
- Return to the main workbook and you see the “Los_Angeles” worksheet copied to the end.
Read More: Excel VBA to Copy Multiple Sheets to New Workbook
Method 6 – VBA to Copy the Active Sheet from Selection to the End of a Closed Workbook
We want to copy the active worksheet to the end of a closed workbook. The test workbook looks something like the picture below. In the test workbook, we want to insert a worksheet that is currently active in the main workbook.
Step 1:
- Close the “Test-Workbook” and click on any worksheet (i.e., “Boston”) in the main workbook.
Step 2:
- Insert a Module in the Microsoft Visual Basic window.
- In the Module, paste the following macro code.
Public Sub Copy_Active_Sheet_ToClosedWorkbook()
Dim fileType
Dim closedBook As Workbook
Dim activeSheet As Worksheet
fileType = Application.GetOpenFilename("Excel Files (*.xlsm), *.xlsm")
If fileType <> False Then
Application.ScreenUpdating = False
Set activeSheet = Application.activeSheet
Set closedBook = Workbooks.Open(fileType)
activeSheet.Copy After:=closedBook.Sheets(closedBook.Worksheets.Count)
closedBook.Close (True)
Application.ScreenUpdating = True
End If
End Sub
1 – begins the macro by setting the Sub name.
2 – declares the variables as Workbook and Worksheet.
3 – assigns the fileType variable to Excel file types (i.e., xlsm). For these Excel file types, variables are assigned to the active worksheet and about to open the workbook.
4 – activeSheet.Copy copies the active worksheet at the end of a closed workbook. The closed workbook is subject to selection from your computer device.
5- updates the workbook and end the conditional execution.
Step 3:
- The F5 key is used to run the macro. This opens the device directory to choose an xlsm file.
- Select the “Test-Workbook” and click on Open.
The active worksheet gets copied at the end of the selected Excel file.
- Open the “Test-Workbook”, and you’ll see the “Boston” worksheet at the end.
Download the Excel Workbooks
Related Articles
- VBA to Copy Excel Worksheet to Another Workbook without Formulas
- VBA to Copy Worksheet to Another Workbook and Rename
