Excel VBA: Copy a Worksheet to Another Workbook Safely

To copy a worksheet from one open workbook to another in Excel VBA, use the worksheet’s Copy method and specify a position in the destination workbook. For example, sourceSheet.Copy After:=destinationBook.Sheets(destinationBook.Sheets.Count) adds a copy at the end; the sample macro below shows how to set up and check the workbooks.

What the VBA Copy Method Does

The Worksheet.Copy method copies a whole worksheet, rather than just the values in its cells. The copied sheet generally includes its formulas, formatting, column widths, page setup, and worksheet objects such as charts and shapes. This makes it useful when a report or template needs to move intact between files.

Use Before or After to identify where the new sheet belongs. The position must be a sheet in the destination workbook. If you call Copy without either argument, Excel creates a new workbook containing the copied sheet instead. That behavior is convenient in some cases, but it is not the same as adding the sheet to a workbook you already have open.

A sheet copy is not a promise that every dependency will become self-contained. Formulas that refer to other sheets, defined names, external data connections, pivot tables, and workbook-level settings can depend on the original workbook. Check important formulas and features after copying, especially when you plan to close the source file or distribute the destination.

Prepare the Workbooks and Macro

Open both the source workbook and the destination workbook in desktop Excel for Microsoft 365 on Windows. The examples assume that the macro is stored in the source workbook and that the source sheet is named Summary. If the macro is stored elsewhere, such as in another workbook or PERSONAL.XLSB, identify the source workbook explicitly instead of assuming ThisWorkbook refers to it.

Save a backup copy of the workbooks before running the macro. VBA actions cannot be reversed with Ctrl+Z.
Save the workbook containing the macro as an Excel Macro-Enabled Workbook (.xlsm), or store the macro in another suitable macro-enabled location.
Confirm the exact worksheet tab name and destination workbook filename, including its extension.
In Excel for Windows, press Alt+F11 to open the Visual Basic Editor. On a Mac, the equivalent is usually Option+F11; some keyboards require Fn+Option+F11.
Choose Insert > Module in the editor, paste the procedure below, and run it while both workbooks are open.

Excel for the web can store a workbook that contains VBA, but it cannot run VBA macros. Use the Excel desktop app for this procedure. Excel for Mac supports VBA, although keyboard shortcuts and some security settings differ from Windows.

Example: Copy a Sheet to an Open Workbook

This procedure copies Summary from the workbook containing the macro into the open workbook named Report.xlsx. It places the new sheet after the destination’s last sheet. Change the two names to match your files and sheet tab.

Sub CopySummaryToOpenWorkbook()
    Dim sourceSheet As Worksheet
    Dim destinationBook As Workbook
    Dim copiedSheet As Worksheet

    On Error GoTo CopyError

    ' The macro is assumed to be stored in the source workbook.
    Set sourceSheet = ThisWorkbook.Worksheets("Summary")
    Set destinationBook = Workbooks("Report.xlsx")

    ' Add the copy after the final sheet in the destination.
    sourceSheet.Copy After:=destinationBook.Sheets(destinationBook.Sheets.Count)
    Set copiedSheet = destinationBook.Sheets(destinationBook.Sheets.Count)

    MsgBox "Copied " & copiedSheet.Name & " to " & destinationBook.Name
    Exit Sub

CopyError:
    MsgBox "The worksheet was not copied: " & Err.Description, vbExclamation
End Sub

The declarations make the workbook and worksheet references explicit, reducing the risk of copying the wrong active sheet. ThisWorkbook means the workbook that contains the running VBA code. It is different from ActiveWorkbook, which means the workbook currently active in Excel and can change as the macro runs.

The line assigning copiedSheet works because the copy is inserted after the destination’s final sheet, so the copy becomes the final sheet. The error handler displays a message for common failures, such as a misspelled tab name or a destination workbook that is not open. It does not undo a partial operation, so inspect the destination if an error appears after the copy step.

Choose the Correct Source and Destination

Workbook references are a frequent source of VBA mistakes. If your macro is stored in a controller workbook rather than the source file, replace ThisWorkbook with a reference to the open source workbook. For example, assign Workbooks("Source.xlsx") to a declared Workbook variable, then use that variable to select the worksheet. The filename in the collection must match the workbook’s open name.

A macro that relies on ActiveSheet or ActiveWorkbook may appear to work when run manually and then copy something different when triggered by a button or another procedure. Prefer named workbook and worksheet variables when the operation matters. You can also add checks that the destination exists and that a sheet with the intended name is not already present before copying.

If the destination workbook is not open, the example cannot find it through the Workbooks collection. Open it first, or write a separate routine to open a known file path and handle cases such as a missing file, a read-only file, or a workbook that is already open. Avoid hard-coding a path that changes between users’ computers unless the location is controlled.

Names, Formulas, and What Comes Along

Excel normally assigns the copied sheet a name that avoids a direct naming conflict, but the result may not be the exact tab name you want. To give it a specific name, set copiedSheet.Name after the copy. First make sure that name is not already used in the destination; worksheet names must be unique within a workbook and cannot contain certain characters. A naming error can occur after the sheet has already been copied, so handle that step deliberately.

Review formulas that refer to cells outside the copied sheet. If a formula depends on another worksheet in the source workbook, the destination may need a corresponding sheet, or Excel may retain a link to the source file. A formula that uses only cells on the copied sheet is less likely to need another workbook, but inspect representative formulas rather than assuming all references behave alike.

Also check defined names, charts, data validation, conditional formatting, and external connections if they are important to the result. Some resources are scoped to a worksheet and others to the workbook, so a copied sheet may not reproduce the original workbook’s full environment. For a report used by others, test the destination after saving, closing, and reopening it.

Common Errors and Practical Fixes

Subscript out of range: Check that the workbook is open and its name is spelled exactly as shown in Excel’s title bar or the VBA Workbooks collection. Check the worksheet tab name as well.
Copy method failed: Confirm that the destination is a valid workbook with at least one sheet and that it is not protected or otherwise unavailable for changes.
Unexpected workbook receives the sheet: Replace implicit active-workbook references with explicit variables. Confirm that destinationBook points to the intended file before calling Copy.
The new tab has an unexpected name: Excel may adjust the copied sheet’s name to avoid a conflict. Rename it only after checking that the requested name is available.
The macro will not run: Use desktop Excel, confirm the file containing the macro is saved in a macro-capable format, and follow your organization’s macro security policy. Do not enable all macros just to run an unfamiliar file.

When copying a worksheet repeatedly, consider what should happen if a copy already exists. You might stop with a clear message, delete and replace the old copy, or create a dated name. Deleting a sheet is destructive, so build in a confirmation or backup step rather than silently removing an existing report.

Saving and Macro Security

Copying a sheet does not automatically save the destination workbook. Save it explicitly after reviewing the result, either with Excel’s Save command or with a deliberate VBA save instruction. If the destination is an .xlsx file, it cannot store VBA project code. A workbook that needs to retain VBA should be saved in a macro-enabled format such as .xlsm.

The macro’s own storage format and the destination’s format are separate decisions. A macro in the source .xlsm can copy a sheet into an .xlsx destination when the destination does not need to retain VBA code. If the worksheet relies on event procedures or other VBA code, check that the code is present and appropriate in the destination, and use a macro-enabled format where needed.

Only run macros from sources you trust. On Windows, macro controls are available through File > Options > Trust Center > Trust Center Settings > Macro Settings, though workplace policies may manage these choices. Macro settings and menus differ on Mac. Avoid lowering security globally; use approved files and trusted locations according to your organization’s guidance.

When to Use Copy and When to Use Other Methods

Use Worksheet.Copy when you need a full worksheet and its layout moved together. If you only need the displayed results, copying and pasting values may be safer because it avoids bringing formulas, links, and some workbook dependencies along. If you need to combine data from many sheets into one table, a data-import or consolidation approach may be more appropriate than duplicating whole tabs.

For a one-time transfer, Excel’s interface can do the job without VBA: right-click the sheet tab, choose Move or Copy, select the destination workbook in the “To book” list, and choose whether to create a copy. The destination must be open to appear in that list. VBA is more useful for repeatable tasks, batch processing, or a button-driven workflow, but it deserves extra care because it can change files without an easy undo.

Frequently Asked Questions

Can VBA copy a worksheet into a workbook that is not open?
Not with the open-workbook example as written. Open the destination first, or modify the macro to open it from a file path, check that opening succeeded, and then copy the sheet. Save and close it only when your workflow calls for that.

How do I copy a worksheet to a new workbook?
Call the worksheet’s Copy method without a Before or After argument. Excel creates a new workbook with that sheet as its contents, and the new workbook becomes active. Assign the new workbook to a variable before saving it so your code can refer to it explicitly.

Does copying a worksheet copy formulas and formatting?
It copies the worksheet rather than just its cell values, so formulas and formatting generally come with it. References to other sheets or workbooks may still depend on the source, so inspect formulas and links in the destination.

Why does Excel say the worksheet name already exists?
Sheet names must be unique within a workbook. Excel may give the copied sheet an adjusted name, or a later rename instruction may fail if that name is already taken. Check the destination’s sheet names before assigning a specific one.

Can I run this VBA in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run the procedure; the basic worksheet-copy method is available in desktop Excel on both platforms.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.