Macro to Rename Every Worksheet in a Workbook

These VBA macros rename all the sheets in a workbook at once: by adding a prefix, by using the value in cell A1, or by numbering them in order.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Add a prefix to every sheet name

Sub AddPrefixToSheets()
    Dim ws As Worksheet, p As String
    p = InputBox("Prefix to add:", , "2026_")
    If p = "" Then Exit Sub
    For Each ws In Worksheets
        ws.Name = Left(p & ws.Name, 31)
    Next ws
End Sub

Left(...,31) keeps each name within Excel’s 31-character limit.

Rename each sheet from cell A1

Useful when every sheet has a title in A1, such as a customer or month name:

Sub RenameSheetsFromA1()
    Dim ws As Worksheet
    On Error Resume Next
    For Each ws In Worksheets
        If ws.Range("A1").Value <> "" Then ws.Name = Left(ws.Range("A1").Value, 31)
    Next ws
    On Error GoTo 0
End Sub

Sheets with an empty A1, or a value that can’t be a sheet name, keep their current name.

Number the sheets in order

Sub NumberSheets()
    Dim i As Long
    For i = 1 To Worksheets.Count
        Worksheets(i).Name = "Week " & i
    Next i
End Sub

Replace "Week " with any label. This fails if a sheet with one of the new names already exists further along.

Things to know

  • Sheet names can be up to 31 characters and can’t contain \ / ? * [ ] :.
  • Formulas update automatically when a sheet is renamed, so references keep working.
  • Formulas that build a sheet name as text, such as INDIRECT, don’t update and will need changing.

Related macros