VBA Copy and Paste Worksheet: Practical Examples

To copy an entire worksheet in Excel VBA, use the worksheet’s Copy method and specify where the copy should go: Worksheets("Sales").Copy After:=Worksheets(Worksheets.Count) adds a copy at the end of the same workbook. To copy only cells, use a range’s Copy Destination:= argument instead. Save a separate copy of your workbook before running a macro: VBA actions cannot be undone with Ctrl+Z.

How worksheet copying works in VBA

A worksheet is an object in Excel’s workbook model. Its Copy method duplicates the sheet, including its cells and much of its sheet-level structure, such as formatting, column widths, row heights, charts, and worksheet objects. You can place the copy before or after another sheet by supplying the Before or After argument. Use one of those arguments, not both.

This is different from copying a selection of cells. A worksheet copy creates another tab; a range copy puts cells into a destination on a sheet that already exists. Choosing the right operation matters: copying a whole sheet may also duplicate elements you did not intend to carry over, while copying a range will not recreate the source sheet’s full layout or sheet-level settings.

VBA uses the workbook and worksheet names in your code to identify the objects. If you leave out a workbook reference, Excel may use whichever workbook is active when the macro runs. Explicit references help prevent a macro from copying the wrong sheet when multiple files are open.

Copy a worksheet to the end of the same workbook

This example copies the Sales worksheet to the end of the workbook that contains the macro, then gives the copy a new name. It assumes a worksheet named Sales exists and the name Sales Archive is not already in use.

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

    ' Identify the source in the workbook containing this macro.
    Set sourceSheet = ThisWorkbook.Worksheets("Sales")

    ' Add a copy after the last worksheet in that workbook.
    sourceSheet.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    Set copiedSheet = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

    ' Worksheet names must be unique within a workbook.
    copiedSheet.Name = "Sales Archive"
End Sub

ThisWorkbook means the workbook in which the VBA code is stored. That may differ from ActiveWorkbook, which means the workbook currently active in Excel. If the macro is stored in your personal macro workbook or an add-in, ThisWorkbook may not be the file whose worksheet you want to copy. In that case, explicitly identify the target workbook rather than assuming the active one.

The copy is added next to the specified sheet. In the example, the last worksheet is used as the placement point, so the new tab appears at the end. If the workbook’s structure is protected, Excel may prevent adding or moving a sheet until that protection is removed by an authorized user.

Copy a worksheet into another open workbook

To copy a sheet into a workbook that is already open, refer to both workbooks. The example below expects a source sheet named Sales in the workbook containing the macro and an open destination workbook named Summary.xlsx. Change those names to match the files you are using.

Sub CopySalesToOpenWorkbook()
    Dim sourceSheet As Worksheet
    Dim destinationBook As Workbook

    ' The source is in the workbook containing this code.
    Set sourceSheet = ThisWorkbook.Worksheets("Sales")

    ' The destination workbook must already be open.
    Set destinationBook = Workbooks("Summary.xlsx")

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

The workbook name in Workbooks("Summary.xlsx") must match the open workbook’s name, including its extension. If the workbook is not open, that reference will fail. You can open it separately in code, but be deliberate about the file path and avoid silently creating or overwriting files.

To put the copy before a particular tab, use that sheet with Before, for example sourceSheet.Copy Before:=destinationBook.Worksheets("Read Me"). The destination must contain the named worksheet. If the destination workbook has no worksheets, there is no existing sheet to use as a Before or After anchor; copying a worksheet without a destination places it in a new workbook instead.

Copy a worksheet into a new workbook

Calling Copy without a Before or After argument creates a new workbook containing the copied sheet. Excel makes that new workbook active. You can capture it with ActiveWorkbook immediately after the copy, then save it using an appropriate path if the workflow requires a file.

Sub CopySalesToNewWorkbook()
    Dim sourceSheet As Worksheet
    Dim newBook As Workbook

    ' Get the source sheet from the workbook containing this macro.
    Set sourceSheet = ThisWorkbook.Worksheets("Sales")

    ' Copy without Before or After to create a new workbook.
    sourceSheet.Copy
    Set newBook = ActiveWorkbook

    ' The new workbook is now available as newBook for further work.
End Sub

Be cautious if adding more steps after this code: the active workbook changes when the new workbook is created. Use the saved newBook variable to refer to it reliably. A copied sheet may also contain formulas linked to other sheets or workbooks; creating a new workbook does not automatically make every formula self-contained.

Copy cells instead of the entire worksheet

For a smaller task, copy a range to a destination range on another worksheet. This example copies cells A1:D20 from Sales to A1 on Report. Both worksheets are in the workbook containing the macro.

Sub CopySalesRange()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet

    Set sourceSheet = ThisWorkbook.Worksheets("Sales")
    Set destinationSheet = ThisWorkbook.Worksheets("Report")

    ' Copy the cells, including formulas and formatting, to the destination.
    sourceSheet.Range("A1:D20").Copy Destination:=destinationSheet.Range("A1")
End Sub

The destination’s top-left cell determines where the copied block begins. This method does not duplicate an entire worksheet tab, and the destination sheet’s other cells remain in place. If the copied range overlaps existing content, the copied cells replace that content in the affected area.

If you want values only, direct assignment avoids the clipboard and does not copy formatting. The source and destination ranges must have the same dimensions. For the same A1:D20 layout, a values-only assignment is destinationSheet.Range("A1:D20").Value = sourceSheet.Range("A1:D20").Value. This copies calculated values rather than formulas. It also does not carry across number formats, borders, comments, or other cell formatting.

What gets copied, and what to check afterward

A whole-sheet copy is useful for preserving a template or creating a working snapshot, but the copy is not necessarily independent of its source. Review the details that matter to your workbook before using the duplicated sheet as a separate record or sending it to someone else.

Formulas and links: Formulas are copied with the sheet. References to cells on the same sheet typically adjust as Excel copies them, while formulas that refer to other sheets or workbooks can continue to point to those original sources. Check important formulas and external links.
Names: Defined names can be workbook-scoped or worksheet-scoped. Name conflicts and scope can affect how formulas resolve after copying, especially when the destination already has names with the same identifiers.
Formatting and layout: A sheet copy generally preserves cell formatting, dimensions, and many objects. Inspect print settings, page breaks, hidden rows or columns, and the position of charts or shapes if the copy is intended for a report.
Code and controls: A worksheet may have associated code, controls, or other objects. Results depend on how those items are set up and on the destination workbook. Check any macro buttons or event-driven behavior instead of assuming every control will work identically.
Protection: Sheet protection and workbook structure protection are different. Protection settings can affect whether a sheet can be edited or whether the macro can add, rename, or move tabs. Follow the workbook owner’s rules rather than trying to bypass protection.

For a dated snapshot, decide whether you need live formulas or fixed values. A sheet copy preserves formulas, so later changes in referenced data can change displayed results. If the purpose is an immutable record, consider copying the relevant range and replacing formulas with values in a controlled process, while retaining any required formatting and documenting what was frozen.

Common errors and how to prevent them

A frequent error is a “subscript out of range” message. It often means a workbook or worksheet name in the code does not match an open workbook or existing tab. Names must be exact; extra spaces and differences in punctuation can matter. Confirm the tab and file names, and remember that Workbooks("Summary.xlsx") refers to an open workbook, not a file merely saved on disk.

A “name already exists” error can occur when the macro assigns a worksheet name that is already used in that workbook. Worksheet tab names must be unique, cannot exceed 31 characters, and cannot contain the characters :, \, /, ?, *, [, or ]. If a macro runs repeatedly, choose a new name each time or check for an existing name before renaming the copy.

Another common problem is relying on the active sheet or workbook. A user may click another file or tab before starting the macro, changing what an unqualified reference means. Use explicit workbook and worksheet variables, and use ThisWorkbook only when the code is stored in the workbook that contains the intended source.

If copying is blocked, check whether the workbook structure is protected, whether the source or destination workbook is available, and whether the destination sheet name is valid. Avoid suppressing all errors with broad error-handling statements: they can hide the cause and leave a partially completed task. For valuable files, test on a copy and verify the result before using the macro on production data.

Run the macro safely

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste a procedure into the module. Return to Excel, press Alt+F8, select the macro, and choose Run. On Mac, use Option+F11 to open the Visual Basic Editor; the precise key behavior can depend on the keyboard and macOS function-key settings. You can also open the editor from the Developer tab.

If the Developer tab is not visible on Windows, enable it through File > Options > Customize Ribbon and select Developer. On Mac, use Excel > Settings (or Preferences in some releases) > Ribbon & Toolbar to show it. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros; use the desktop Excel app.

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain its VBA project. Excel may show a security warning when opening macros. Only enable macros for files from sources you trust, and do not weaken security settings just to run an unfamiliar file. Make a separate backup before running a macro because VBA changes cannot be reversed with Ctrl+Z.

Frequently Asked Questions

How do I copy a worksheet and keep its formatting?
Use the worksheet’s Copy method with a Before or After destination. A whole-sheet copy preserves the sheet layout and formatting more fully than copying only a range. Review formulas, links, and objects in the duplicate afterward.

How do I copy a worksheet to the end of a workbook?
Use sourceSheet.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count), replacing sourceSheet with a worksheet variable that refers to the source. This places the copy after the workbook’s last worksheet.

How do I copy a worksheet without formulas?
A whole-sheet copy retains formulas. To make a values-only version, copy the needed range and assign its Value to an equally sized destination range. This transfers displayed/calculated values, not formulas or formatting.

Can VBA copy a worksheet to a closed workbook?
The destination workbook generally needs to be open for the worksheet copy operation. A macro can open a destination file first, copy the sheet into it, and save it, but it should use an explicit path and handle existing files carefully.

Why does my copied worksheet have broken or unexpected formulas?
Copied formulas may still refer to source sheets, other workbooks, or defined names. Inspect the formula bar and workbook links, and decide whether the copy should remain connected to its sources or be converted to values for a fixed snapshot.

Can I undo a VBA worksheet copy with Ctrl+Z?
No. Running VBA clears Excel’s normal undo history, so Ctrl+Z cannot reverse the macro’s changes. Save a backup first; if the result is wrong, close without saving or restore the backup.

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.