To append a worksheet with Excel VBA, copy it after the destination workbook’s last sheet. The macro below copies the Data sheet from the workbook containing the code into an already-open Archive.xlsx, then gives the copy a unique name if needed.
What “append a worksheet” means in Excel
In this context, appending usually means adding a complete worksheet tab to the end of a workbook. It does not mean adding rows to the bottom of a table. VBA’s Worksheet.Copy method performs the sheet-level operation: it creates a copy of a worksheet and places it before or after a specified sheet.
The example uses the last sheet in the destination workbook as the insertion point. If the destination has tabs named Summary, January, and February, the copied sheet appears after February. If you instead want to add records to an existing sheet, that is a different task: you would find the next empty row and copy or assign the data there.
VBA macro to copy a sheet to the end
Before running the macro, open both workbooks. Put the code in the workbook that contains the sheet to copy, and change Data and Archive.xlsx to match your sheet and destination workbook. The macro checks the destination’s sheet names and adds a number in parentheses when the source name is already taken.
Option Explicit
Sub AppendWorksheet()
Dim sourceWb As Workbook
Dim destinationWb As Workbook
Dim sourceWs As Worksheet
Dim copiedWs As Worksheet
Dim baseName As String
Dim candidateName As String
Dim suffix As Long
' The workbook containing this code is the source.
Set sourceWb = ThisWorkbook
Set destinationWb = Workbooks("Archive.xlsx")
Set sourceWs = sourceWb.Worksheets("Data")
' Find an unused name before copying the sheet.
baseName = sourceWs.Name
candidateName = baseName
suffix = 2
Do While SheetNameExists(destinationWb, candidateName)
candidateName = Left$(baseName, 31 - Len(CStr(suffix)) - 3) _
& " (" & suffix & ")"
suffix = suffix + 1
Loop
' Copy after the final tab, then apply the selected name.
sourceWs.Copy After:=destinationWb.Sheets(destinationWb.Sheets.Count)
Set copiedWs = destinationWb.Worksheets(destinationWb.Worksheets.Count)
copiedWs.Name = candidateName
End Sub
Private Function SheetNameExists(ByVal wb As Workbook, _
ByVal sheetName As String) As Boolean
Dim sh As Object
For Each sh In wb.Sheets
If StrComp(sh.Name, sheetName, vbTextCompare) = 0 Then
SheetNameExists = True
Exit Function
End If
Next sh
End Function
For example, if Archive.xlsx already has a sheet called Data, the copied sheet will be called Data (2). If that name is also present, the macro tries Data (3), and continues until it finds an unused name. The shortened base name in the code leaves room for the suffix within Excel’s 31-character worksheet-name limit.
How to add and run the macro
- Save a working copy of the workbook before running any macro. VBA actions generally cannot be reversed with Ctrl+Z.
- In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. On Mac, Option+F11 may work; some keyboards also require the Fn key.
- In the editor, select the source workbook in the Project pane, choose Insert > Module, and paste the code into the module.
- Change the source sheet name and destination workbook name in the code. The destination workbook must be open, and its filename must match exactly.
- Return to Excel and run
AppendWorksheetfrom the Macro dialog, opened with Alt+F8 on Windows. Save the destination workbook after checking the new tab.
Save a workbook that contains VBA in the Excel Macro-Enabled Workbook format (.xlsm) if you need to retain the code. Excel for the web can open and edit many workbooks, but it cannot run VBA macros. Excel desktop for Mac supports VBA, though some Windows-specific code and keyboard shortcuts may need adjustment.
What the copied worksheet includes
Copying a worksheet is different from copying a range of cells. The new tab generally carries the worksheet’s cell contents, formulas, formatting, column widths, and sheet-level objects such as charts and shapes. It is useful when a report or template needs to travel as a coherent sheet rather than as a block of values.
However, a sheet copy is not a complete copy of every dependency in the source workbook. A formula that refers to another sheet may continue to refer to that sheet in the original workbook. If Excel cannot resolve a reference within the destination, it may retain or create a link to the source file. Workbook-level items such as some defined names, connections, or supporting sheets may also need separate attention. After copying, inspect formulas, named ranges, charts, and any data connections that matter to the task.
Choosing the right insertion point
The macro inserts the new worksheet after the final tab, which is what most users mean by “append.” To insert it next to a particular worksheet instead, change the After argument to that worksheet. For example, if the destination contains a tab named Summary, use After:=destinationWb.Worksheets("Summary") to place the copy immediately after Summary.
To put a copy at the beginning, use the Before argument with the first sheet in the destination. Be deliberate about whether you mean the first worksheet or the first tab: a workbook can also contain chart sheets. The sample uses the Sheets collection to find the final tab, and checks all sheet tabs when looking for a duplicate name.
Common errors and how to prevent them
- Subscript out of range: Excel cannot find the workbook or worksheet named in the code. Check spelling, file extensions, spaces, and whether the destination workbook is open.
Workbooks("Archive.xlsx")refers to an open workbook, not a file that VBA opens automatically. - Macro uses the wrong source workbook:
ThisWorkbookmeans the workbook where the code is stored. If the code is in Personal.xlsb or another macro workbook, setsourceWbto the actual source workbook instead. - Worksheet name error: Excel limits worksheet names to 31 characters and does not allow certain characters, including a colon and a slash. The suffix logic handles the length of generated names, but a manual name change must also obey Excel’s naming rules.
- Unexpected external links: A copied formula may refer to a sheet that was not copied. Check formulas and use Excel’s link-management tools where available; do not assume that copying one sheet also brings across every workbook dependency.
- Macro is blocked or missing after saving: Use a macro-enabled workbook format and follow your organization’s macro-security policy. Do not turn on all macros globally just to run a single procedure.
For troubleshooting, run the macro with both workbooks open and verify the exact tab and file names before adding error-handling code. Broad error suppression, such as leaving On Error Resume Next active throughout a macro, can hide a failed copy or an incorrect workbook reference.
Copying a worksheet versus copying its data
Use Worksheet.Copy when the destination needs a new tab that retains the sheet’s layout and objects. Use a range-copy approach when the goal is to transfer only a table, selected cells, or values into an existing destination sheet. A range approach also gives more control over whether formulas, formatting, or values are transferred, but it requires you to decide where the data belongs.
Another common misconception is that “append” always means combine all monthly sheets into one list. If each workbook has a similar data table and you want one consolidated table, copying whole tabs will preserve separate tabs rather than combine their rows. For recurring imports, Power Query or a purpose-built VBA routine that appends table rows may be more suitable. Choose based on the desired result, not just the word “append.”
When to use a different approach
A VBA macro is useful for a repeatable task, especially when a process has to copy a named sheet into a named workbook on a schedule or as part of a larger procedure. For a one-time copy, right-click the worksheet tab, choose Move or Copy, select the destination workbook, and select Create a copy. That manual route avoids writing code and makes the insertion position visible before you confirm.
If the sheet is being moved rather than duplicated, be careful: moving removes it from the source workbook. The sample uses Copy, so the original remains in place. If you need to automate a batch of files, first define how to handle duplicate tabs, missing sheets, protected workbooks, links, and save locations. Those decisions matter more than simply making the copy command run.
Frequently Asked Questions
How do I append a worksheet to the end of the same workbook?
Set both sourceWb and destinationWb to the same workbook, then use the same copy statement. For a macro stored in that workbook, assigning both variables to ThisWorkbook is one option. The duplicate-name check still matters because the new tab cannot use a name already in the workbook.
Does Worksheet.Copy move the original sheet?
No. Worksheet.Copy creates a copy and leaves the original where it is. The separate Worksheet.Move method moves a sheet, so use it only if removing the tab from its original workbook is intended.
Can I append a worksheet when the destination workbook is closed?
Not with the sample as written. The destination must be open because the code refers to it through the Workbooks collection. VBA can be extended to open a file first, but that requires a valid file path and a decision about whether to save and close the destination afterward.
Can I append several worksheets at once?
Yes. A routine can loop through a list of source sheet names and copy each one, applying a unique-name check to every copy. Validate that each requested sheet exists and decide how to handle a partial run before using a batch macro on important files.
Will the copied worksheet’s formulas still work?
Often, but not necessarily exactly as before. References to other sheets or workbook-level names may point to the source workbook or depend on items that were not copied. Review formulas and links in the destination, especially before sharing or deleting the source file.
Related
- Excel Text to Columns: Find It and Split Data Correctly
- Excel Text to Columns: Advanced Options and Uses
- Excel Text to Columns: Split Alt+Enter Line Breaks
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: How to Check Whether a Workbook Is OpenTo check whether a workbook is open in Excel VBA, loop through Application.Workbooks and compare each open workbook’s…
- Excel Error 9: Fix Subscript Out of Range in VBA SafelyExcel Error 9, “Subscript out of range,” is a VBA runtime error: a macro has tried to retrieve…
- VBA Code for Sending Email From ExcelTo create an email from Excel VBA, automate classic Outlook for Windows: use CreateObject("Outlook.Application"), create a mail item,…
- VBA Excel Active Workbook Path: Get It CorrectlyIn Excel VBA, use ActiveWorkbook.Path to get the folder path of the workbook that is active; it does…
- Copy and Paste Values in VBA: Methods and ExamplesTo copy only cell values with Excel VBA, assign one range’s Value2 to another range of the same…
- Option Button in Excel VBA: Add, Group, and Use ItYes. In Excel for Microsoft 365 on Windows, add an option button from Developer > Insert, then choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.