VBA Rename Sheet in Excel — What a Rename Updates and What It Silently Breaks

TL;DR — ws.Name = "Sales 2026" renames a sheet. The line is trivial; the consequences are not. A rename is a refactor: Excel rewrites every reference it can parse — cell formulas, defined names, chart series, validation lists — and leaves every reference that is just text pointing at a name that no longer exists: INDIRECT strings, hyperlink targets, and your own macros that say Worksheets("Sales"). Before you rename, know which of the two your workbook depends on. Build names through a sanitizer, never straight from a cell, and anchor your own code on the CodeName, which no rename can touch.

Sub RenameMonthSheet()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Template")
    ws.Name = "Report " & Format(Date, "yyyy-mm")   ' never a raw date: 9/30/2026 contains /
End Sub

This is the first of a three-part cluster on a sheet's identity. The tab name is a label the user owns. Rename changes that label, hide takes the tab off screen without taking the sheet out of reach, and check if a sheet exists asks whether a label is taken before you trust it. Code that must survive users anchors on something they cannot change; code that has to use a name checks it first.

What you'll learn

• The mental model: a rename is a refactor with two kinds of references
• What Excel updates for you, and what it silently leaves broken
• The naming rules behind error 1004, and a sanitizer that respects them
• Why naming a sheet from a date cell works in one country and crashes in another
• Renaming in bulk, and swapping two names without a collision
• Reading a sheet name back, in VBA and in a formula

The mental model: a rename is a refactor

Every reference to a sheet is one of two kinds. A parsed reference is something Excel understands as a pointer — =Sales!B4 in a cell, a defined name that refers to Sales!$A$1:$A$50, a chart series, a data validation list. Excel stores these as pointers to the sheet, so when the name changes it rewrites the text for you: =Sales!B4 becomes ='Sales 2026'!B4, quotes added because of the space.

A text reference is a string that happens to spell the name. Excel has no idea it points anywhere, so a rename leaves it exactly as it was — now pointing at nothing:

That table is the whole article in miniature. The rename itself never fails silently; its text references do.

Rename a sheet, and read its name back

The mechanics are one property, read and write:

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sales")

Debug.Print ws.Name              ' read: "Sales"
ws.Name = "Sales 2026"           ' write: the tab changes at once
Debug.Print ActiveSheet.Name     ' the name of whatever sheet is active

Hold the sheet in a variable before you rename it, as above. Once the name changes, Worksheets("Sales") no longer finds it, but the variable still points at the same sheet object — so the rest of the macro keeps working. That is the same reason your long-lived code should use the sheet's CodeName (Sheet3, set in the VBA editor's Properties window): it is a parsed reference inside VBA, and the Worksheets guide shows why no tab rename can break it. The CodeName is read-only while code runs, so you cannot rename a sheet's CodeName from a macro the way you rename its tab.

To show the sheet name in a cell, use a formula instead of code:

' In a cell (Excel 365):  =TEXTAFTER(CELL("filename", A1), "]")
' Returns the name of the sheet the formula sits on; empty until the workbook is saved.

The naming rules behind error 1004

Excel refuses a name that breaks any of its rules, and every refusal is the same run-time error 1004:

• 31 characters at most, and not empty.
• None of : \ / ? * [ ].
• No apostrophe as the first or last character.
• Not the same as another sheet in the workbook, ignoring case (sales collides with Sales).
• Not History, which English Excel reserves for its change-tracking sheet.

The Add Sheet guide covers these where a new sheet gets its first name. For renames, the practical answer is to never assign a name you did not clean. A small sanitizer handles every rule except uniqueness:

Function SafeSheetName(ByVal s As String) As String
    Dim ch As Variant
    For Each ch In Array(":", "\", "/", "?", "*", "[", "]")
        s = Replace(s, ch, "-")
    Next ch
    s = Left$(Trim$(s), 31)
    Do While Left$(s, 1) = "'"
        s = Mid$(s, 2)
    Loop
    Do While Right$(s, 1) = "'"
        s = Left$(s, Len(s) - 1)
    Loop
    If Len(s) = 0 Then s = "Sheet"
    SafeSheetName = s
End Function

Truncate before stripping apostrophes, not after: cutting to 31 characters can expose an apostrophe at the new end.

The trap: naming a sheet from a cell

The most common rename in real workbooks is "name each sheet after the value in A1". It looks harmless:

ws.Name = ws.Range("A1").Value     ' A1 holds a date

If A1 holds a date, .Value returns a Date, and VBA turns it into text using the computer's regional short-date format. On a German PC that is 30.09.2026 — a perfectly legal sheet name. On a US PC it is 9/30/2026, and the slashes raise error 1004. The same macro passes every test in Munich and crashes in Chicago. Numbers carry a quieter version of the same problem: the decimal separator changes with the region too.

The fix is to decide the text yourself, then sanitize it:

Dim v As Variant
v = ws.Range("A1").Value
If IsDate(v) Then
    ws.Name = SafeSheetName(Format(v, "yyyy-mm-dd"))
Else
    ws.Name = SafeSheetName(CStr(v))
End If

Format with an explicit pattern produces the same text on every machine. That is the rule for any name built from data: you format it, not the region settings.

Renaming in bulk, and swapping two names

Bulk renames fail on collisions, usually halfway through, leaving a workbook half-renamed. Two habits prevent that. First, make every target name unique before you assign it, using the sheet-exists check:

Function UniqueSheetName(wb As Workbook, ByVal base As String) As String
    Dim nm As String, n As Long
    nm = base
    Do While SheetExists(nm, wb)
        n = n + 1
        nm = Left$(base, 31 - Len(" (" & n & ")")) & " (" & n & ")"
    Loop
    UniqueSheetName = nm
End Function

Second, when two sheets trade names, go through a temporary name. Renaming A to B while B still exists is a duplicate and raises 1004; a three-step swap never collides:

Sub SwapSheetNames(ws1 As Worksheet, ws2 As Worksheet)
    Dim n1 As String, n2 As String
    n1 = ws1.Name: n2 = ws2.Name
    ws1.Name = "~swap~"
    ws2.Name = n1
    ws1.Name = n2
End Sub

Renaming only the capitalization — sales to Sales — is not a collision: it is the same sheet, and Excel accepts it.

The judgment call: search for text references before you rename

A rename you do by hand has the same effect as one done in code, so the decision is not about VBA. It is about which kind of reference the workbook depends on. If everything that points at the sheet is a parsed reference, rename freely: Excel does the refactor. If anything spells the name as text — INDIRECT, a hyperlink, a macro, a Power Query step that reads the sheet by name — search for the old name first (Ctrl+F, Look in: Formulas, Within: Workbook, and Ctrl+Shift+F in the VBA editor) and fix those in the same change. My rule: rename sheets the user sees, but never make your code depend on what they are called. Keep your macros on CodeNames or on variables you Set once, and a rename stays what it should be — a change of label.

How ExcelMaster helps

Rename problems show up far from the rename: an INDIRECT that turns into #REF! next month, a macro that dies with error 9 the first time a colleague tidies the tabs, a monthly sheet that fails only on the laptop set to US dates.

ExcelMaster lets you describe the job — "rename each sheet after the region in A1, and fix everything that still points at the old names" — and writes the rename with sanitized, unique names, then finds the text references that a rename would leave behind and updates them in the same pass.

Frequently asked questions

How do I rename a sheet in VBA?

Set its Name property: Worksheets("Sheet1").Name = "Sales", or ActiveSheet.Name = "Sales" for the active sheet. Hold the sheet in a variable first if the macro keeps using it afterwards, because Worksheets("Sheet1") no longer finds it once the name has changed.

Why do I get error 1004 when renaming a sheet?

The name breaks a rule: it is longer than 31 characters, empty, contains one of : \ / ? * [ ], starts or ends with an apostrophe, is History, or matches another sheet in the workbook regardless of case. Clean the name with a sanitizer and check it is not already taken before you assign it.

Do formulas update when I rename a sheet?

Formulas that reference the sheet directly, like =Sales!B4, update automatically, and so do defined names, charts and validation lists. References written as text do not: INDIRECT("Sales!B4"), hyperlink targets, and VBA strings like Worksheets("Sales") keep the old name and break.

How do I rename a sheet from a cell value?

Read the cell, turn the value into text yourself, then sanitize it: use Format(value, "yyyy-mm-dd") for dates and CStr for everything else, and pass the result through a function that removes illegal characters. Assigning a date cell directly makes the name depend on the PC's regional date format.

How do I get the name of the active sheet in VBA?

Read ActiveSheet.Name. For a specific sheet, read ws.Name on a worksheet variable. To display a sheet's own name in a cell, use =TEXTAFTER(CELL("filename", A1), "]") in Excel 365, which works once the workbook has been saved.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-30.

Related guides: VBA Hide Sheet · VBA Check If Sheet Exists · VBA Worksheets · VBA Add Sheet · VBA Copy Sheet · VBA Format · VBA Replace · VBA Named Range