Excel VBA: Copy a Worksheet and Rename It

To copy a worksheet and rename the copy with VBA, use the worksheet’s Copy method, then set the new sheet’s Name property. In Excel for Microsoft 365 on Windows, this macro copies the “Template” sheet to the end of the workbook and names the copy “June Report”:

Sub CopyAndRenameSheet()
    Dim sourceWs As Worksheet
    Dim copiedWs As Worksheet

    ' Copy the template after the last worksheet in this workbook.
    Set sourceWs = ThisWorkbook.Worksheets("Template")
    sourceWs.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

    ' The copied sheet is now the last worksheet in the workbook.
    Set copiedWs = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    copiedWs.Name = "June Report"
End Sub

Replace the two sheet names with names that suit your workbook. Before running any macro, save a copy of the workbook: VBA actions cannot be undone with Ctrl+Z.

How the copy-and-rename code works

sourceWs is a variable that points to the original worksheet. The line using Copy After:= creates a separate worksheet immediately after the specified one. In this example, the specified sheet is the last worksheet in ThisWorkbook. The final two lines identify the new last worksheet and assign its tab name.

The After argument controls where the copy is placed. You can instead use Before:= to put it before a particular worksheet. For example, use sourceWs.Copy Before:=ThisWorkbook.Worksheets("Summary") to place the copy in front of a worksheet named “Summary.” Use either Before or After, not both.

Do not leave out the destination argument unless you intend to create a new workbook. When Copy is called without Before or After, Excel creates a new workbook containing the copied sheet. That behavior can surprise a macro that is meant to add a tab to the current file.

Choose the correct workbook and source sheet

A common source of mistakes is using a worksheet name without being clear about which workbook contains it. ThisWorkbook means the workbook that contains the VBA code. ActiveWorkbook means the workbook currently active in Excel. Those can be different—for example, when the macro is stored in the Personal Macro Workbook or an add-in. For a macro saved in the workbook it edits, ThisWorkbook is usually the more dependable choice.

Qualify both the source and destination references when possible. A bare reference such as Worksheets("Template") may resolve against the active workbook, which can change while a macro runs. A fully qualified reference makes the intended workbook explicit and helps prevent copying a sheet from the wrong file.

Use ThisWorkbook.Worksheets("Template") when the source is in the file containing the macro.
Use ActiveWorkbook.Worksheets("Template") only when the active workbook is deliberately the target.
Use a workbook variable when copying between two open workbooks, so the destination is not left to chance.

The example identifies the copy as the last worksheet after inserting it after the last worksheet. Excel also allows chart sheets in a workbook, but the Worksheets collection contains worksheet tabs rather than chart sheets. If your file has a particular mix of sheet types or complex event code, test the placement logic on a duplicate file first.

Use a unique name when the macro may run again

A fixed name such as “June Report” works once, but a second run will fail if a worksheet with that name already exists. Worksheet names must be unique within a workbook, and Excel treats names as case-insensitive for this purpose. One practical approach is to append a number when the requested name is already taken. The following code can be placed in a standard VBA module; it copies the template and uses names such as “June Report,” “June Report 2,” and “June Report 3.”

Sub CopyTemplateWithUniqueName()
    Dim wb As Workbook
    Dim sourceWs As Worksheet
    Dim copiedWs As Worksheet
    Dim baseName As String
    Dim candidate As String
    Dim suffix As Long

    ' Use the workbook containing this code.
    Set wb = ThisWorkbook
    Set sourceWs = wb.Worksheets("Template")
    baseName = "June Report"
    candidate = baseName
    suffix = 2

    ' Find an unused name before making the copy.
    Do While WorksheetNameExists(wb, candidate)
        candidate = baseName & " " & suffix
        suffix = suffix + 1
    Loop

    ' Copy the template and assign the available name.
    sourceWs.Copy After:=wb.Worksheets(wb.Worksheets.Count)
    Set copiedWs = wb.Worksheets(wb.Worksheets.Count)
    copiedWs.Name = candidate
End Sub

Private Function WorksheetNameExists(ByVal wb As Workbook, _
                                     ByVal sheetName As String) As Boolean
    Dim ws As Worksheet

    ' Compare names without regard to letter case.
    For Each ws In wb.Worksheets
        If StrComp(ws.Name, sheetName, vbTextCompare) = 0 Then
            WorksheetNameExists = True
            Exit Function
        End If
    Next ws
End Function

The helper function checks existing worksheet tabs before the copy is made. This avoids the duplicate-name error for the base name and its numbered alternatives. The requested base name still needs to follow Excel’s naming rules; the function does not sanitize invalid characters or shorten a name that is too long.

Worksheet naming rules to remember

Excel sheet names can contain up to 31 characters. A name cannot be blank, cannot contain a colon, backslash, slash, question mark, asterisk, or square brackets, and cannot begin or end with an apostrophe. It also cannot duplicate another sheet name in the same workbook, even if the difference is only capitalization. Names such as April: Sales or Report/Final therefore cause a run-time error when assigned.

When names come from cells or user input, validate them before setting Name. A simple fixed name is often safer for a small automation task. For a repeated monthly process, consider a naming pattern that is both valid and distinct, such as a month plus a year. Also check whether the full generated name stays within Excel’s 31-character limit.

Copy a worksheet to another open workbook

To copy a sheet from one open workbook into another, set variables for both files and specify the destination workbook’s last worksheet. The example assumes the macro is stored in the source workbook and that the destination file is already open. Change the workbook and worksheet names to match your files.

Sub CopySheetToOpenWorkbook()
    Dim sourceWb As Workbook
    Dim targetWb As Workbook
    Dim sourceWs As Worksheet
    Dim copiedWs As Worksheet

    ' Identify the source and destination explicitly.
    Set sourceWb = ThisWorkbook
    Set targetWb = Workbooks("Annual Reports.xlsx")
    Set sourceWs = sourceWb.Worksheets("Template")

    ' Insert the copy at the end of the destination worksheets.
    sourceWs.Copy After:=targetWb.Worksheets(targetWb.Worksheets.Count)
    Set copiedWs = targetWb.Worksheets(targetWb.Worksheets.Count)
    copiedWs.Name = "June Report"
End Sub

The destination workbook must be open and the named worksheet must exist. If the destination already has a “June Report” tab, choose another name or add a uniqueness check. A copied sheet may bring along formulas, formatting, objects, and worksheet-level settings. However, formulas that refer to other sheets not included in the copy can retain references to the original workbook or become external links. Review important formulas and links after copying across files, especially before sharing the destination.

Common errors and how to fix them

If Excel stops on the line that assigns Name, check for a duplicate name, an invalid character, or a name longer than 31 characters. If it stops on the Copy line, confirm that the source and destination worksheets exist and that the destination workbook is open. A workbook protected at the structure level may prevent sheets from being added, deleted, moved, or copied; remove that protection only if you are authorized to do so.

“Subscript out of range”: Check the spelling of the workbook and worksheet names. Names in VBA must match the tabs or open workbook titles.
Duplicate-name error: Choose a new tab name or use code that checks for an available name first.
Macro does not appear to run: Confirm that macros are enabled for a trusted file and that the code is in a standard module or is being called from the correct place.
The wrong workbook changes: Replace ambiguous active-workbook references with explicit workbook variables or ThisWorkbook.
The copied sheet has unexpected links: Inspect formulas that refer to worksheets outside the copied sheet, particularly after copying between workbooks.

Save a workbook that contains VBA in a macro-enabled format such as .xlsm. If you save it as a macro-free .xlsx file, Excel does not retain VBA code in that file. Keep a separate backup before running code that changes workbook structure, since Ctrl+Z does not reverse VBA operations.

Run the macro and consider non-VBA options

On Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the procedure, and run it from the editor or from Excel’s Macro dialog. You can open the Macro dialog with Alt+F8. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer.

On a Mac, the Visual Basic Editor shortcut is commonly Option+F11; some keyboards require Fn as well. Menu and keyboard details can vary by keyboard and Excel build, so the Developer tab or Excel’s Tools > Macro menu can be used to locate macro commands. Excel for the web does not create or run VBA macros. Use the desktop Excel app for this VBA workflow.

For a one-time copy, VBA may be unnecessary. In desktop Excel, right-click the worksheet tab, choose Move or Copy, select the destination workbook and position, and check Create a copy. Then rename the new tab by double-clicking its name or using the sheet-tab context menu. The manual method is a sensible alternative when you do not need repeatable automation; VBA is useful when the same copy-and-name operation must be performed regularly or across many workbooks.

Frequently Asked Questions

How do I copy a worksheet to the end of a workbook in VBA?
Use sourceWs.Copy After:=wb.Worksheets(wb.Worksheets.Count), then refer to the new last worksheet and set its Name. Qualify wb as the workbook you actually want to change.

Why does VBA create a new workbook when I copy a sheet?
If you call Worksheet.Copy without a Before or After destination, Excel places the copied worksheet in a new workbook. Supply one of those arguments to insert it into an existing workbook.

Can I copy and rename a sheet in one VBA line?
The copy and rename are separate operations: Copy creates the new worksheet, and Name changes its tab name. Keeping them on separate lines makes it easier to identify which step caused an error.

Can VBA copy a sheet and keep its formatting?
Yes. The worksheet Copy method copies the sheet itself, including its cell formatting and many sheet-level contents. Check external references and workbook-specific links when the copy goes to another file.

Can I undo a worksheet copy macro with Ctrl+Z?
No. Excel clears the normal undo history when VBA changes the workbook, so Ctrl+Z cannot reliably reverse a macro. Save a backup copy before running it.

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.