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

Excel VBA is a strong tool to achieve any criteria-driven outcome. Some instances like Excel VBA copy sheet to end or anywhere in a workbook is very common. And Excel VBA Macros do it with ease.

Let’s say, we have a workbook where 2 similar worksheets exist. The names of the worksheets are NewYork and Boston. And the name of the workbook is Excel VBA Copy sheet to End.xlsm. This workbook is our main workbook. However, we have another workbook named Test-Workbook.xlsx where only 1 worksheet exists named Los_Angeles.

Dataset-Excel VBA Copy Sheet to End

In this article, we demonstrate multiple variants of the Excel VBA Macros to copy sheet to end using above mentioned 2 workbooks.


Download Excel Workbook


⧭ Opening Microsoft Visual Basic and Inserting Code in the Module

Before proceeding to demonstrate any methods, it’s necessary to know the ways to open and insert a Module in Microsoft Visual Basic in Excel.

🔄 Opening Microsoft Visual Basic: There are mainly 3 ways to open Microsoft Visual Basic window.

🔼 Using Keyboard Shortcuts

 Press ALT+F11 altogether to open Microsoft Visual Basic window.

🔼 Using Developer Tab

In an Excel worksheet, Go to Developer Tab > Select Visual Basic. The Microsoft Visual Basic window appears.

developer tab-Excel VBA Copy Sheet to End

🔼 Using Worksheet Tab

Go to any worksheet, Right-Click on it > Choose View Code (from the Context Menu).

worksheet option-Excel VBA Copy Sheet to End

 🔄 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 > Right-Click on it > Select Insert (from the Context Menu) > then Choose Module.

Insert module

🔼 You can also do it by Selecting Insert (from the Toolbar) > then Choosing Module.

insert module-Excel VBA Copy Sheet to End


6 Easy Ways to Copy Sheet to End Using Excel VBA

Method 1: Using Excel VBA to Copy Specific Sheet to End in Same Workbook

We have a workbook named Excel VBA Copy Sheet to End.xlsm and the workbook have 2 worksheets. One is NewYork and the other one is Boston. In this method, we want to copy a specific sheet (i.e., NewYork) to the end of this workbook.

method 1-Excel VBA Copy Sheet to End

Step 1: Follow one of the ways mentioned in the earlier instruction section to open the Microsoft Visual Basic window and insert a Module.

In the Module paste the following code.

Sub Copy_Sheet_inSameWorkbook()
Sheets("NewYork").Copy After:=Sheets(Sheets.Count)
End Sub

Macro code

➤ in the code,

1 – start the macro procedure by declaring the Sub name. You can assign any name to the code.

2 – copy the NewYork sheet in the same workbook using VBA Sheets.Copy.

Step 2: Press F5 to run the Macro. After returning to the workbook, you see the NewYork worksheet copied to the end.

Copy worksheet-Excel VBA Copy Sheet to End

Read More:How to Copy a Sheet in Excel (5 Ways)


Method 2: Using Excel VBA to Copy Active Sheet to End in Same Workbook

Now, in the Excel VBA Copy Sheet to End.xlsm workbook we want to copy the active worksheet (i.e., Boston) to the end of the same workbook.

method 2-Excel VBA Copy Sheet to End

Step 1: Open the Microsoft Visual Basic and then Insert a Module following the instruction section.

Type the below macro code in the Module.

Sub Copy_ActiveSheet_ToEnd()
ActiveSheet.Copy After:=Sheets(Sheets.Count)
End Sub

macro code

➤ From the above image, in the sections,

1 – begin the macro code declaring the VBA Macro Code’s Sub name.

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

inserted

Read More: VBA Copy Sheet in Excel (13 Quick Ways)


Method 3: Copy Specific Sheet to End from an Open Workbook to Another Using Excel VBA

From the beginning, we have two workbooks. One works as the main workbook (i.e., Excel VBA Copy Sheet to End.xlsm) and the other as the test workbook (i.e., Test-Workbook.xlsm). Both of the workbooks are open. And for this method, we want to copy a specific sheet (i.e., NewYork) from our main workbook to the test workbook.

From the below screenshot, we can see both of the workbooks are open.

method 3-Excel VBA Copy Sheet to End

Step 1: Repeat the instructions, to open Microsoft Visual Basic and insert the Module section.

Write 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

macro code

➤ The code’s sections,

1 – initiate the macro procedure declaring the Sub name.

2VBA Sheets.Copy copies of the specific sheet at the end of the test workbook (i.e., Test-Workbook.xlsm).

Step 2: Press F5 to run the macro and it pastes the NewYork worksheet at the end of the test workbook.

Copied

Read More: Excel VBA to Copy and Rename a Worksheet Multiple Times


Method 4: VBA to Copy Sheet to End of a Closed Workbook

Now, what if we want to copy and paste a worksheet at the end of a closed workbook. As we have two open workbooks, we close the test workbook. We then run a macro in the main workbook’s module to copy the test workbook’s specific worksheet at its end.

We know by now there are two worksheets in the test workbook. They are Los_Angeles and NewYork as shown in the below picture. After closing the test workbook, we want the Los_Angeles sheets at the end of the test workbook.

method 4

Step 1: In the Module type 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

macro code

➤ From the above image, the code’s sections,

1 – take forward the macro by setting the Sub name.

2 – declare the variable as a Workbook.

3 -assign the object variable as False (in order not to update) and mrfbook to a source workbook.

4 – Workbooks function takes Test-Workbook.xlsm as the copy to workbook. And Sheets.Copy copies the Los_Angeles worksheet at its end.

5 – close the Test-Workbook.xlsm and update the workbook.

Step 2: In order to run the macro press F5. The macro copies the Los_Angeles sheet and places it at the end of the workbook similar to the following image.

Inserted worksheet

Read More: VBA to Copy Excel Worksheet to Another Workbook without Formulas


Method 5: Copy Sheet from Another Workbook to End Without Opening Using Excel VBA

In a scenario, where we have to copy a worksheet from another workbook without opening it. For that reason, we have multiple worksheets in the main workbook as depicted. In the previous workbook, we closed the test workbook. Now, we want to copy the test workbook’s Los_Angeles sheet in the main workbook and place it at the end.

method 5-Excel VBA Copy Sheet to 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

macro

➤ The code is marked in parts,

1 – begin the macro by setting the Sub name.

2 – declaring the variable as Workbook.

3 – assign object variable and Workbook to a source workbook (i.e., mrfbook).

4 – copy 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 – assign the object variable to update the workbook.

Step 2: Push the F5 key to run the Macro. Afterward, return to the main workbook and you see the Los_Angeles worksheet copied to the end.

Copied and placed at the end

Read More: How to Copy Worksheet to Another Workbook Using VBA


Method 6: VBA to Copy Active Sheet from Selection to End of a Closed Workbook

On the contrary to Method 5, 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.

method 6

Step 1: Close the Test Workbook and click on any worksheet (i.e., Boston) in the main workbook.

active sheet

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

macro code

➤ The macro code is numbered in parts,

1 – begin the macro by setting the Sub name.

2 – declaring the variables as Workbook and Worksheet.

3 – assign 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.

4activeSheet.Copy copies the active worksheet at the end of a closed workbook. The closed workbook is subjected to selection from your computer device.

5– update the workbook and end the conditional execution.

Step 3: The F5 key is used to run the macro. After running the macro, it opens the device directory to choose a xlsm file from.

Select the Test-Workbook > Click on Open.

The active worksheet gets copied at the end of the selected excel file.

directory

➤ Now, open the Test-Workbook.xlsm, you see the Boston worksheet at the end.

Final outcome-Excel VBA Copy Sheet to End

Any worksheet that you select works as an active worksheet for the main workbook. You can switch the workbooks to copy from and to.

Read More: VBA to Copy Worksheet to Another Workbook and Rename (5 Methods)


Conclusion

In this article, we use multiple VBA Macro codes to achieve the Excel VBA copy sheet to end scenario. We copy specific, active worksheets to the end of the same workbook. As well as copy the worksheet at the end of an open or closed or vice versa. Hope these above-mentioned codes do your job. Comment if you have further inquiries or have anything to add.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo