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 are 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”. This workbook is our main workbook. However, we have another workbook named “Test-Workbook” where only 1 worksheet exists named “Los_Angeles”.
In this article, we demonstrate multiple variants of the Excel VBA Macros to copy sheet to end using above mentioned 2 workbooks.
⧠Opening Microsoft Visual Basic and Inserting Code in the Module in Excel
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 three main ways to open the Microsoft Visual Basic window.
🔼 Using Keyboard Shortcuts
 Press ALT + F11 altogether to open the Microsoft Visual Basic window.
🔼 Using Developer Tab
In an Excel worksheet, Go to Developer Tab > Select Visual Basic. The Microsoft Visual Basic window appears.
🔼 Using Worksheet Tab
Go to any worksheet, Right-Click on it > 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 > Right-click on it > Select Insert (from the Context Menu) > then Choose Module.
🔼 You can also do it by Selecting Insert (from the Toolbar) > then Choosing Module.
Excel VBA Copy Sheet to End: 6 Easy Ways
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” and the workbook has 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.
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
➤ 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.
Method 2: Using Excel VBA to Copy Active Sheet to End in Same Workbook
Now, 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: 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
➤ 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.
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”) and the other as the test workbook (i.e., “Test-Workbook”). Both of the workbooks are open. 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.
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
➤ The code’s sections,
1 – initiate 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 pastes 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 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.
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
➤ 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 – The Workbook function takes “Test-Workbook” as the copy of a workbook. And Sheets.Copy copies the “Los_Angeles” worksheet at its end.
5 – close the “Test-Workbook” 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.
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.
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
➤ 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.
Read More: Excel VBA to Copy Multiple Sheets to New Workbook
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.
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
➤ 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.
4 – activeSheet.Copy copy the active worksheet at the end of a closed workbook. The closed workbook is subject 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 an xlsm file.
Select the “Test-Workbook” > Click on Open.
The active worksheet gets copied at the end of the selected Excel file.
➤ Now, open the “Test-Workbook”, you see the “Boston” worksheet at the 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.
Download Excel Workbook
Conclusion
In this article, we use multiple VBA Macro codes to achieve the Excel VBA copy sheet to the 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
- VBA to Copy Excel Worksheet to Another Workbook without Formulas
- VBA to Copy Worksheet to Another Workbook and Rename
