How to Copy Sheet to End in Excel Using VBA (6 Methods)

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.

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.

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: