Excel VBA Copy Worksheet: Code, Methods, and Safety

To copy a worksheet with Excel VBA, call the worksheet’s Copy method and specify where the duplicate should go. For example, sourceSheet.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) adds a copy after the last sheet in the workbook containing the macro. Save a separate copy of your workbook before running VBA: macros cannot be undone with Ctrl+Z.

How the VBA Copy Method Works

In Excel for Microsoft 365 on Windows, a worksheet is an object, and its Copy method duplicates that sheet. You can place the copy before or after another worksheet by supplying the Before or After argument. The destination sheet determines the workbook that receives the copy. If you omit both arguments, Excel creates a new workbook containing the copied sheet.

Use one placement argument at a time. The argument takes a worksheet object, not a sheet name written as plain text. In a macro, the object is commonly set first with a workbook and worksheet reference. That explicit reference also helps avoid copying the wrong sheet when a different workbook or tab happens to be active.

A copied sheet is more than a range of cells. It can include formulas, formatting, row heights, column widths, print settings, charts, shapes, and other sheet content. Excel adjusts formulas as it normally does when copying a sheet; formulas that refer to other sheets or workbooks may continue to point to their existing sources, so check important links in the duplicate.

Copy a Sheet to the End of the Same Workbook

This example copies a worksheet named January to the end of the workbook that contains the VBA code. Change the sheet name to match your workbook exactly. The workbook must contain a worksheet with that name.

Option Explicit

Sub CopySheetToEnd()
    Dim sourceSheet As Worksheet

    ' Identify the worksheet in the workbook containing this code.
    Set sourceSheet = ThisWorkbook.Worksheets("January")

    ' Place the duplicate after the workbook's last worksheet.
    sourceSheet.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
End Sub

ThisWorkbook means the workbook where the macro code is stored. It does not necessarily mean the workbook currently selected in Excel. That distinction matters if the code is in the Personal Macro Workbook, an add-in, or a separate controller file. If the source data is in another workbook, identify that workbook explicitly instead of assuming ThisWorkbook is the source.

Excel generally creates a name for the duplicate automatically when the original name is already taken, such as a numbered variation of the original. Do not rely on a particular automatically generated name in later code; name the copied worksheet explicitly if subsequent steps need to refer to it.

Copy a Worksheet to Another Open Workbook

To copy between workbooks, set references to both workbooks and the source sheet, then use a worksheet in the destination workbook as the placement point. The example below assumes both files are already open and their names, including extensions, match the entries in the Workbooks collection.

Option Explicit

Sub CopySheetToAnotherWorkbook()
    Dim sourceBook As Workbook
    Dim targetBook As Workbook
    Dim sourceSheet As Worksheet

    ' Both workbooks must already be open in Excel.
    Set sourceBook = Workbooks("Source.xlsx")
    Set targetBook = Workbooks("Report.xlsx")
    Set sourceSheet = sourceBook.Worksheets("January")

    ' Add the copied sheet after the destination's last worksheet.
    sourceSheet.Copy After:=targetBook.Worksheets(targetBook.Worksheets.Count)
End Sub

If Excel reports that it cannot find a workbook, check the displayed workbook name in the title bar and include the correct file extension. The destination workbook must have a worksheet available to use as the insertion point. This macro copies the sheet into the open destination workbook; it does not save that workbook to disk. Save it separately when you are satisfied with the result.

Copy a Worksheet into a New Workbook

When neither Before nor After is provided, Excel puts the copied sheet in a new workbook. This is useful when you want to send one tab as a separate file or create a standalone working copy.

Option Explicit

Sub CopySheetToNewWorkbook()
    Dim sourceSheet As Worksheet
    Dim copiedSheet As Worksheet

    ' Find the source sheet in the workbook containing this code.
    Set sourceSheet = ThisWorkbook.Worksheets("January")

    ' Copy without Before or After to create a new workbook.
    sourceSheet.Copy
    Set copiedSheet = ActiveSheet

    ' The new workbook is now active; save it using Excel's Save As command.
End Sub

After the copy, Excel activates the new workbook and its copied sheet. The macro above leaves saving to you, so you can choose a folder, file name, and suitable file type. If the new workbook must retain VBA code, use a macro-enabled format such as .xlsm; saving a workbook containing macros as .xlsx does not preserve its VBA project.

Rename the Duplicate and Avoid Name Conflicts

Worksheet names must be unique within a workbook, cannot exceed 31 characters, and cannot contain the characters \ / ? * : [ ]. If you want a predictable name, keep a reference to the copied sheet and assign its name after copying. For example, add the following lines after the copy statement in the same-workbook macro:

Dim copiedSheet As Worksheet

sourceSheet.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
Set copiedSheet = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
copiedSheet.Name = "January Backup"

Declare copiedSheet with the other variables near the start of the procedure, before executable statements. The sample name must not already belong to another sheet in that workbook. If a macro may run repeatedly, first decide how it should handle an existing copy: stop with a message, delete and replace it, or generate a unique name. Deleting a sheet is destructive, so do not silently delete an existing tab unless that behavior is intentional.

What Gets Copied—and What Does Not

Copying a whole worksheet differs from copying and pasting cells. A sheet copy carries the worksheet as a unit, including its layout and many sheet-level objects. A cell-range copy transfers only the selected cells and whatever cell properties are included in the paste operation; it does not reproduce the entire worksheet structure or all objects on the tab.

Formulas and values: Both are carried with their cells. Relative references may adjust in the copied sheet, while external or cross-sheet references can still lead to the original sources.
Visual layout: Cell formatting, widths, heights, page setup, and many charts or shapes are part of a sheet-level copy.
Sheet-specific code: Worksheet code associated with the copied sheet may be carried with it. Workbook-level code, such as code in ThisWorkbook or a standard module, is not made part of a copied sheet. Check the VBA project and test any event-driven behavior in the destination.
Workbook context: A copied sheet does not automatically bring along every other sheet or workbook-level setting it references. Defined names, links, connections, and formulas can therefore behave differently in a workbook with a different structure.

For instance, a formula on January that refers to a summary tab may still depend on that summary tab after January is copied into a new workbook. If the referenced tab is absent, the formula may show an error or retain a link to another workbook. Review formulas, named ranges, data connections, and macros when a duplicate is meant to be independent.

Common Errors and Practical Fixes

Most copy-macro failures come from an incorrect reference, a name collision, or a destination that is not available—not from the Copy method itself. Check these points before changing the code:

Subscript out of range: The workbook or worksheet name in the code does not match an open workbook or existing tab. Confirm spelling, spaces, and file extension.
Wrong workbook copied: The code uses ActiveWorkbook or ActiveSheet when another file is active. Store explicit workbook and worksheet references instead.
Invalid worksheet name: The requested name is too long, contains a prohibited character, or is already in use. Choose a valid, unused name.
Destination reference fails: Confirm the target workbook is open and has a worksheet available for the After or Before argument.
Macros appear missing: A worksheet copy is not a full copy of the source workbook’s VBA project. Check where the code is stored and save the resulting workbook in a macro-enabled format if its macros must remain.

When debugging, step through the procedure with the Visual Basic Editor and inspect the workbook and worksheet variables before the copy line. A fully qualified reference is usually more reliable than changing Excel’s selection or relying on whichever tab is active.

Use Excel’s Interface for a One-Time Copy

If you only need one duplicate, VBA may be unnecessary. In Excel for Microsoft 365 on Windows, right-click the worksheet tab, choose Move or Copy, select the destination workbook and insertion position, tick Create a copy, and choose OK. The same dialog can move a sheet if you leave the copy option unticked, so check that box when the original must remain in place.

You can also drag a worksheet tab while holding Ctrl on Windows to make a copy within the workbook. On Mac, hold Option while dragging the tab. The Move or Copy dialog is clearer when copying to a different open workbook or when you need to select a specific insertion point. In Excel for the web, VBA macros cannot run; use the available sheet-tab options or open the workbook in the desktop application to run VBA.

When VBA Is the Better Choice

A manual copy is straightforward for an occasional task. VBA is useful when the same operation must be repeated, when a report needs a dated or named duplicate, or when a larger process copies sheets into several workbooks. A macro can also make the source and destination explicit, reducing mistakes caused by selecting the wrong tab.

Before automating a repeated copy, decide what should happen on a second run. Should the macro keep every dated copy, overwrite a prior copy, or stop if a name already exists? Define how formulas and external links should behave, too. Those choices are business rules, not decisions Excel can safely infer from the request to copy a sheet.

Frequently Asked Questions

How do I copy a worksheet in VBA without moving the original?
Use the worksheet’s Copy method with Before or After. That creates a duplicate and leaves the source sheet in place. Do not use a move operation if both sheets are needed.

How do I copy a worksheet to a new workbook in VBA?
Call sourceSheet.Copy without a Before or After argument. Excel creates and activates a new workbook containing the copied worksheet. Save that workbook in the appropriate file format.

Can I copy a worksheet and rename it in the same macro?
Yes. Copy the sheet, store a reference to the new worksheet, and set its Name property. The chosen name must be valid and not already used in the destination workbook.

Why does my copied worksheet still refer to the original workbook?
Copying a sheet does not guarantee that every formula, named range, or data connection becomes self-contained. Inspect formulas and links, especially when the source sheet refers to other tabs or external files.

Can I undo a worksheet-copy macro with Ctrl+Z?
No. Running a VBA macro clears Excel’s normal undo history, so Ctrl+Z cannot reverse its changes. Save a separate workbook copy before running the macro, particularly if the code deletes, replaces, or renames sheets.

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.