VBA Copy and Paste a Sheet: Code and Options

To copy a worksheet with VBA, use the sheet’s Copy method and specify where the duplicate should go. For example, Worksheets("Template").Copy After:=Worksheets(Worksheets.Count) adds a copy at the end of the same workbook; the examples below show complete macros for common destinations.

What copying a sheet means in VBA

In Excel, a worksheet is more than the visible values in its cells. Copying the whole sheet generally carries its cell contents, formulas, formatting, column widths, row heights, and worksheet-level features such as charts and shapes. That is why Worksheet.Copy is usually the right method when the goal is to make a working duplicate of a tab.

This differs from copying a range of cells and pasting it elsewhere. A range operation affects only the selected cells, while a worksheet copy creates another sheet. Formula references may adjust as Excel copies the sheet, and references to other sheets or workbooks may continue to point to their original locations. Review important formulas and links after copying, especially in a workbook that uses external data or references.

These examples are for desktop Excel for Microsoft 365 on Windows. VBA macros do not run in Excel for the web. Excel for Mac supports VBA, though keyboard shortcuts and some security or interface details can differ.

Copy a worksheet within the same workbook

Use After to place the duplicate after a particular sheet, or use Before to place it ahead of one. This complete macro copies a sheet called Template to the end of the workbook and then gives the new sheet a name.

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

    ' Identify the sheet to duplicate.
    Set sourceSheet = ThisWorkbook.Worksheets("Template")

    ' Add its copy at the end of this workbook.
    sourceSheet.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

    ' The copied sheet is now the last worksheet.
    Set copiedSheet = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    copiedSheet.Name = "Template Copy"
End Sub

Change Template to the exact tab name in your file. The code uses ThisWorkbook, meaning the workbook containing the macro. If the macro is stored in a separate workbook, such as Personal.xlsb, that may not be the workbook whose sheet you intend to copy; in that case, identify the source workbook explicitly.

The destination name must be available. Excel sheet names cannot be blank, cannot contain certain characters such as a colon or slash, and are limited to 31 characters. If a sheet named Template Copy already exists, the rename line raises an error. You can remove that line to let Excel assign a default name, or add logic that checks for an unused name before renaming.

Copy a sheet to another open workbook

To copy a worksheet into a different workbook that is already open, reference the destination workbook by its name in the Workbooks collection. The workbook name normally includes its file extension, as shown here.

Sub CopySheetToOpenWorkbook()
    Dim sourceSheet As Worksheet
    Dim targetBook As Workbook

    ' The source sheet is in the workbook containing this macro.
    Set sourceSheet = ThisWorkbook.Worksheets("Template")

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

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

Replace Report.xlsx with the actual open workbook name, including the correct extension. If Excel reports that the subscript is out of range, check that the workbook is open and that the name matches exactly. This example does not save the destination workbook; save it separately or add a carefully chosen Save or SaveAs step if the macro should do so.

When a sheet is copied between workbooks, Excel may bring along sheet-level names and references that affect the destination. If the two workbooks have overlapping defined names or formulas linked to other sheets, inspect the result before relying on it.

Copy a sheet into a new workbook

Calling Copy without a Before or After argument creates a new workbook containing the copied worksheet. The new workbook becomes active, so the macro can refer to it with ActiveWorkbook immediately afterward.

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

    ' Select the sheet to copy from this workbook.
    Set sourceSheet = ThisWorkbook.Worksheets("Template")

    ' Copy without a destination to create a new workbook.
    sourceSheet.Copy
    Set newBook = ActiveWorkbook

    ' Rename the single copied worksheet.
    newBook.Worksheets(1).Name = "Template"
End Sub

The macro creates the workbook but does not choose a folder or filename. To keep it, save it from Excel or add a SaveAs statement with an appropriate full path and file format. If a macro-enabled workbook is required, use a macro-enabled format such as .xlsm rather than saving the file as a standard .xlsx workbook, which does not preserve VBA code.

Copy a sheet or paste only its values?

Use a whole-sheet copy when the destination should retain the structure and functionality of the source. If you only need the displayed results and want to remove formulas, copy the used cells and paste values into a destination sheet instead. This example pastes the source sheet’s used range starting at cell A1 on an existing sheet named Archive.

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

    ' Set the source and destination worksheets.
    Set sourceSheet = ThisWorkbook.Worksheets("Template")
    Set destinationSheet = ThisWorkbook.Worksheets("Archive")

    ' Copy the used cells, then paste their values only.
    sourceSheet.UsedRange.Copy
    destinationSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

This approach transfers values rather than formulas and formatting. It does not make a duplicate tab, and it does not clear old content in Archive that falls outside the newly pasted area. Also, Excel’s UsedRange can extend beyond the visibly populated cells if formatting was previously applied far down or across the sheet. For a controlled transfer, specify an exact range instead of relying on UsedRange.

For example, if you want to move just a known block, replace the copy line with a range such as sourceSheet.Range("A1:F40").Copy. Consider whether you need values, formulas, formats, or all three before choosing between a worksheet copy and a range paste.

Choose where the copied sheet goes

The Before and After arguments determine sheet order. They take a worksheet object as their destination, not a text name by itself. For example, to put a copy immediately before a sheet called Summary, the key pattern is sourceSheet.Copy Before:=targetBook.Worksheets("Summary"). To place it immediately after Summary, use After:= instead.

Use a workbook-qualified reference when a macro could involve more than one workbook. An unqualified reference such as Worksheets("Summary") can depend on which workbook is active. A qualified reference such as targetBook.Worksheets("Summary") makes the intended destination clear and reduces errors when the user has several files open.

Use After:=workbook.Worksheets(workbook.Worksheets.Count) to append a copy at the end.
Use Before:=workbook.Worksheets(1) to put a copy at the beginning.
Use a named worksheet as the reference to position the copy beside a relevant tab.

When a workbook has chart sheets as well as worksheets, be aware that the Worksheets collection refers to worksheets, while the broader Sheets collection can include different sheet types. Match the collection to the kind of sheet you are targeting.

Run the macro safely

Before running any macro that changes a workbook, save a separate copy of the file. VBA changes cannot be undone with Ctrl+Z, so a backup is the simplest way to recover from an unexpected copy, rename, or paste. Check that the macro names the intended source and destination before running it on important data.

To add a macro in Excel for Microsoft 365 on Windows, open the Visual Basic Editor from Developer > Visual Basic, then use Insert > Module and paste the procedure into the module. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon. Run a macro from Developer > Macros, or use Alt+F8 on Windows to open the macro list. On Mac, use the Developer tab or Tools > Macro > Macros; keyboard shortcuts can depend on the keyboard and function-key settings.

Only enable macros in files you trust. Macro security settings and prompts are there to reduce the risk of running harmful code; do not lower security broadly just to run an unfamiliar workbook. Excel for the web can open and edit many workbooks, but it cannot run VBA macros, so use the desktop app to execute these procedures.

Common problems and fixes

Subscript out of range: Excel could not find a workbook or sheet named in the code. Check spelling, spaces, file extensions, and whether the workbook is open.
Run-time error when renaming: The requested tab name may already exist or may contain a prohibited character. Choose a valid, unused name.
Copy appears in the wrong workbook: A reference may rely on the active workbook. Qualify the source and destination with explicit workbook variables.
Macro cannot be run: The file may be open in Excel for the web, macros may be blocked, or the workbook may not be saved in a macro-capable format. Open it in desktop Excel and check the file type and security prompt.
Old data remains after a values paste: Paste Special replaces the cells it covers but does not automatically clear extra cells below or to the right. Clear the intended destination area deliberately if that is required.

For code that may be run repeatedly, consider adding checks for the source and destination sheets and deciding what should happen if the copy name already exists. A short macro that works once may need those safeguards before it becomes part of a routine process.

Frequently Asked Questions

What is the VBA command to copy a worksheet?
Use the worksheet’s Copy method, with Before or After to choose its position. For example, sourceSheet.Copy After:=targetBook.Worksheets(targetBook.Worksheets.Count) appends it to the target workbook.

How do I copy a sheet without changing the original?
The Copy method creates a separate worksheet and leaves the original in place. Check formulas and external references in the duplicate, because copied formulas may still refer to the original workbook or other sheets.

Can VBA copy a worksheet to another workbook?
Yes. Set a Workbook variable to an already open destination workbook, then use that workbook’s worksheet as the Before or After destination. The destination workbook must be open for the example using Workbooks("Report.xlsx").

How do I copy a sheet as values only?
Copy the desired range and use PasteSpecial Paste:=xlPasteValues on the destination. This pastes calculated results instead of formulas; it does not create a new worksheet or preserve the source formatting.

Why can’t I undo a VBA sheet copy?
Excel clears its normal undo history when a macro runs. Ctrl+Z will not reverse the macro’s changes, so save a backup before running code that copies, renames, or overwrites workbook content.

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.