VBA Change Worksheet Name: Code and Common Issues

VBA & macros · Updated November 18, 2026

To change a worksheet name with VBA, assign a new value to the worksheet’s Name property, such as Worksheets("Sheet1").Name = "Summary". In a macro, qualify the worksheet with the correct workbook so the code renames the intended tab rather than a sheet in whichever workbook happens to be active.

Basic VBA syntax

A worksheet’s tab name is its Name property. VBA changes it with an assignment: put the worksheet object on the left of the dot, and the new name in quotation marks on the right. The following short macro renames the currently active worksheet:

Sub RenameCurrentWorksheet()
    Dim ws As Worksheet

    ' Use the worksheet that is active when the macro runs.
    Set ws = ActiveSheet
    ws.Name = "Summary"
End Sub

This works when a worksheet is active and the name Summary is available and valid. It is deliberately simple, but it depends on the active selection: if someone activates a different worksheet before running the macro, that other worksheet will be renamed. For repeatable automation, refer to a specific worksheet in a specific workbook instead.

Before running a macro that changes workbook structure, save a separate copy of the workbook. Excel’s Ctrl+Z undo history does not undo VBA macro actions, so a mistaken rename may need to be corrected manually or recovered from a saved copy.

Rename a particular worksheet safely

Use ThisWorkbook when the macro is stored in the workbook it should change. The example below targets the worksheet currently called Sheet1 and checks whether another sheet already uses the proposed name. Excel requires sheet names to be unique across worksheets and chart sheets, so the check looks through the workbook’s Sheets collection.

Sub RenameWorksheetIfAvailable()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim otherSheet As Object
    Dim newName As String
    Dim nameInUse As Boolean

    ' ThisWorkbook is the workbook containing this macro.
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet1")
    newName = "Quarterly Results"

    ' Check all sheet types, but do not compare the target with itself.
    For Each otherSheet In wb.Sheets
        If Not otherSheet Is ws Then
            If StrComp(CStr(otherSheet.Name), newName, vbTextCompare) = 0 Then
                nameInUse = True
                Exit For
            End If
        End If
    Next otherSheet

    If nameInUse Then
        MsgBox "Another sheet already uses that name."
    Else
        ws.Name = newName
    End If
End Sub

The comparison ignores letter case, matching Excel’s treatment of sheet names: for example, Summary and summary are not distinct names. This example checks for a duplicate, but its fixed new name is assumed to satisfy Excel’s other naming rules. If the sheet is not actually called Sheet1, change that reference to its current tab name or use another reliable way to identify it.

ThisWorkbook means the workbook containing the running VBA code. ActiveWorkbook means the workbook currently active in Excel, which may be different. That distinction matters when code is stored in Personal.xlsb, an add-in, or another workbook: in those cases, ThisWorkbook may not be the file whose worksheet you intend to rename. Choose the reference deliberately rather than relying on the active window.

Use a value from a cell as the new name

A macro can take a proposed name from a cell, which is useful when a user supplies a period, department, or report label in the worksheet. In this example, cell A1 on Sheet1 supplies the name. The code trims spaces at the beginning and end and stops if the result is empty:

Sub RenameFromCellA1()
    Dim ws As Worksheet
    Dim newName As String

    ' Read the proposed name from A1 on the worksheet to rename.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    newName = Trim$(CStr(ws.Range("A1").Value))

    If Len(newName) = 0 Then
        MsgBox "Enter a worksheet name in cell A1."
        Exit Sub
    End If

    ' The entered value must still meet Excel's naming rules.
    ws.Name = newName
End Sub

This is a basic example, not a complete validation routine. It will still fail if the cell contains a name that Excel rejects or one already used by another sheet. A production macro should validate the proposed text and check for duplicates before assigning it; it should also decide what to do with cell errors or formulas that return unexpected values. Do not assume that trimming spaces alone makes arbitrary text safe as a worksheet name.

Excel worksheet naming rules

A name that looks reasonable in a report title may not be acceptable as a worksheet tab name. Excel enforces limits and restrictions, and VBA does not bypass them when setting .Name. Apply the rules before building a naming scheme from dates, user input, imported data, or cell contents:

  • A worksheet name can contain no more than 31 characters.
  • It cannot be blank or contain any of these characters: colon (:), backslash (\), slash (/), question mark (?), asterisk (*), or square brackets ([ ]).
  • A name cannot begin or end with an apostrophe.
  • Names must be unique within the workbook, without regard to capitalization. This includes names used by chart sheets, not just worksheets.

For example, April 2026 is a valid style of name if it is not already taken. April/2026 is not valid because it contains a slash, and a 32-character name exceeds the limit. When generating names automatically, decide how to handle long text and forbidden characters rather than silently assuming Excel will shorten or clean it for you. Excel generally reports an error when an assignment violates a naming rule; it does not reliably turn the proposed text into a valid name on your behalf.

Worksheet tab name versus VBA CodeName

Excel exposes two identifiers that are easy to confuse. The worksheet tab name is the visible label users see and the value changed by ws.Name. A worksheet also has a VBA CodeName, visible in the Visual Basic Editor’s Project Explorer and Properties window. Changing the tab name does not change the CodeName.

For example, a worksheet might have the CodeName Sheet1 while its tab reads Quarterly Results. In VBA, code in the same project can often refer to the sheet by its CodeName, such as Sheet1.Range("A1"), even after the visible tab is renamed. That can make internal code less dependent on users changing tab labels. By contrast, a reference such as Worksheets("Quarterly Results") uses the tab name and must match it exactly.

Use the tab name when the visible label is part of the task or when selecting a sheet by a name stored in data. Use a CodeName when you want VBA code within the project to refer to a particular worksheet even if someone renames its tab. CodeNames are configured in the Visual Basic Editor; assigning to .Name is not a way to change them.

Common errors and troubleshooting

A failed rename is often caused by a mismatch between the code’s assumptions and the workbook’s current state. Check the workbook, target, proposed name, and protection status before changing the macro. Common causes include:

  • Wrong workbook: The macro uses ActiveWorkbook or an unqualified reference, so it acts on a different open file than expected. Set a workbook variable explicitly.
  • Wrong sheet reference: The code asks for Worksheets("Sheet1"), but the tab has already been renamed. Update the reference or identify the worksheet another way.
  • Name already in use: A worksheet or chart sheet already has that name. Check the whole Sheets collection before assignment.
  • Invalid text: The proposed value is blank, too long, contains a forbidden character, or begins or ends with an apostrophe.
  • Protected workbook structure: Workbook structure protection can prevent renaming sheets. If you are authorized to change the workbook, review its protection settings or ask its owner for access; do not attempt to bypass protection.
  • Unexpected active object: ActiveSheet may not be a worksheet. A workbook can have a chart sheet active, which is one reason an explicit worksheet reference is safer.

Excel may show a run-time error when a name is invalid, duplicated, or otherwise unavailable. Rather than suppressing every error with On Error Resume Next, check predictable conditions first and handle any remaining errors intentionally. Broadly ignoring errors can make a macro appear to succeed even when it renamed nothing, or conceal a different problem that needs attention.

Rename multiple worksheets

VBA is especially useful when several sheets need a consistent naming pattern, but batch renaming deserves more care than changing one tab. Before writing a loop, define how the macro identifies its targets, how it generates each name, and what it should do if a generated name conflicts with an existing tab. For example, “rename every sheet” may accidentally include an instructions sheet, a chart sheet, or a template that should keep its name.

A dependable batch workflow is to:

  1. Save a separate copy of the workbook and test the procedure there.
  2. Specify the target sheets explicitly, or filter them using a clear rule such as a known prefix.
  3. Generate all proposed names and check their length, characters, and uniqueness before applying changes.
  4. Decide how to report a conflict instead of overwriting, skipping silently, or stopping halfway without explanation.
  5. Run the rename and review the resulting tabs and any formulas, links, or procedures that refer to sheet names.

One subtle issue is that a rename sequence can create a temporary collision. If two sheets are being swapped from names A and B to names B and A, renaming the first directly to B fails because B is still occupied. A robust swap uses a temporary valid name, then assigns the final names after both original names have been freed. For larger batches, plan the order or use unique temporary labels and check them too.

Running the macro and choosing an alternative

In Excel for Microsoft 365 on Windows, open the workbook in the desktop app, then use Developer > Visual Basic to open the VBA editor. Insert or select a standard module, place the procedure there, and run it from the editor or from Excel’s macro interface. If Developer is not visible, enable the Developer tab in Excel’s ribbon options. Save the file in a macro-enabled format such as .xlsm if it needs to retain VBA code.

Excel for the web cannot run VBA macros. Open the workbook in desktop Excel to use this code. Excel for Mac also supports VBA in the desktop application, though the editor and ribbon layout can differ; use the Mac version’s Developer tab and Visual Basic command where available. The worksheet naming property is still Name, but do not assume Windows-specific instructions for opening the editor or managing security settings match every Mac setup.

If you only need to rename one sheet once, VBA is usually unnecessary. Double-click the worksheet tab, type the new name, and press Enter, or use the sheet’s context menu and choose Rename. In desktop Excel for Windows, the Home tab also offers the Rename Sheet command under Format. For repeatable tasks, many sheets, or names generated from data, VBA can save time. For a one-off edit, the interface is simpler and avoids macro security and file-format considerations.

Frequently Asked Questions

What is the VBA command to rename a worksheet?
Assign a new name to the worksheet’s Name property: Worksheets("Sheet1").Name = "Summary". In reusable code, qualify the worksheet with its workbook so the reference is unambiguous.

Can VBA rename a hidden worksheet?
Yes. A hidden worksheet can generally be renamed by referring to it directly in VBA. However, workbook structure protection can prevent sheet changes, and the code still needs a valid, unused name.

Does changing the worksheet name change its CodeName?
No. Assigning to .Name changes the visible tab label. The VBA CodeName is a separate identifier and remains unchanged.

Why does VBA say the name is already in use?
Another sheet in that workbook already has the proposed name. Names are unique without regard to capitalization, and a chart sheet can also occupy the name. Choose a different name or rename the conflicting sheet first.

Can I undo a worksheet rename with Ctrl+Z?
Not if the rename was performed by a VBA macro. Macro actions are not undone through Excel’s normal Ctrl+Z history. Save a copy before running the code, or rename the tab back manually if the change is still clear.

Related

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.