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.
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
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.
🔼 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.
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.
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.
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.
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.
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.
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.xlsm).
Step 2: Press F5 to run the macro and it pastes the NewYork worksheet at the end of the test workbook.
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 – 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.
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.
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 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.
➤ Now, open the Test-Workbook.xlsm, 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.
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.