VBA & Macro Problems

Rename Excel Sheet Tabs from a Worksheet List with VBA

Steve KSteve K Nov 14, 2026 868 views

Question details

Rename Excel worksheet tabs using old and new names stored in a worksheet list.

Rename Excel Sheet Tabs from a Worksheet List with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A workbook has daily tabs that need new weekday and date labels each week based on a separate list.
Observed behavior
The user wants a macro to read the list and update the corresponding worksheet tabs.
Before you start

Create a worksheet named SheetTitles with new names in column A and current tab names in column B, starting in row 2. If column A contains dates or formulas, format and widen the cells so they display the desired tab names rather than number signs.

Solution 1Recommended

Run a VBA macro to rename tabs from a worksheet list

Use this method to update worksheet names in bulk and record the result of each rename in column C.

The macro reads the displayed text in column A and finds the current worksheet name in column B. After a successful rename, it updates column B so the list is ready for the next change.

Names cannot be blank, exceed 31 characters, contain : \ / ? * [ or ], begin or end with an apostrophe, or duplicate an existing sheet name. The macro skips invalid or conflicting names and reports errors in column C.

1
Prepare the list

On SheetTitles, enter headers in A1:C1. Starting in row 2, enter each desired name in column A and its existing worksheet tab name in column B. For example, use Monday 16-3-20 in A2 and the current Monday tab name in B2.

2
Open the VBA editor

In Excel, enable Developer through File > Options > Customize Ribbon if needed. Select Developer > Visual Basic, then choose Insert > Module.

3
Insert the macro

Paste the following code into the module: Option Explicit Sub RenameSheetsFromList() Dim listSheet As Worksheet, target As Worksheet Dim item As Object Dim lastRow As Long, r As Long Dim oldName As String, newName As String Dim conflict As Boolean, errText As String Set listSheet = ThisWorkbook.Worksheets("SheetTitles") lastRow = listSheet.Cells(listSheet.Rows.Count, "B").End(xlUp).Row For r = 2 To lastRow oldName = Trim(CStr(listSheet.Cells(r, "B").Value)) newName = Trim(listSheet.Cells(r, "A").Text) listSheet.Cells(r, "C").Value = "" Set target = Nothing On Error Resume Next Set target = ThisWorkbook.Worksheets(oldName) On Error GoTo 0 If target Is Nothing Then listSheet.Cells(r, "C").Value = "Current worksheet not found" GoTo NextRow End If If target Is listSheet Then listSheet.Cells(r, "C").Value = "List sheet cannot be renamed" GoTo NextRow End If If Not ValidSheetName(newName) Then listSheet.Cells(r, "C").Value = "Invalid new name" GoTo NextRow End If conflict = False For Each item In ThisWorkbook.Sheets If StrComp(item.Name, newName, vbTextCompare) = 0 And _ StrComp(item.Name, target.Name, vbTextCompare) <> 0 Then conflict = True Exit For End If Next item If conflict Then listSheet.Cells(r, "C").Value = "New name already exists" GoTo NextRow End If errText = "" On Error Resume Next target.Name = newName If Err.Number <> 0 Then errText = Err.Description On Error GoTo 0 If errText <> "" Then listSheet.Cells(r, "C").Value = "Rename failed: " & errText Else listSheet.Cells(r, "B").Value = target.Name listSheet.Cells(r, "C").Value = "Renamed" End If NextRow: Next r End Sub Function ValidSheetName(ByVal nameText As String) As Boolean Dim badChars As Variant, ch As Variant If Len(nameText) = 0 Or Len(nameText) > 31 Then Exit Function If Left(nameText, 1) = "'" Or Right(nameText, 1) = "'" Then Exit Function badChars = Array(":", "\", "/", "?", "*", "[", "]") For Each ch In badChars If InStr(1, nameText, CStr(ch), vbBinaryCompare) > 0 Then Exit Function Next ch ValidSheetName = True End Function

4
Save and run

Choose File > Save As and save the workbook as Excel Macro-Enabled Workbook (*.xlsm). In the VBA editor, place the cursor inside RenameSheetsFromList and choose Run > Run Sub/UserForm.

5
Review the results

Return to SheetTitles and check column C. Correct any reported names or conflicts, then run the macro again. For the next weekly update, change column A; column B contains the names from the last successful run.

Run a VBA macro to rename tabs from a worksheet list
Names are processed in order: A direct swap of two existing names will produce conflicts. Rename one tab to a temporary unique name first. Workbook structure protection can also prevent renaming.
Free Microsoft Office alternative

Manage worksheet lists with WPS Spreadsheet

WPS Office is a free, lightweight Office alternative with a familiar interface for opening and editing spreadsheet files. Its Excel format compatibility makes it easy to move worksheet lists between applications, but it does not fix or guarantee execution of this Excel VBA macro.

Edit weekday and date lists alongside worksheet tabsOpen and save Microsoft Excel spreadsheet formats, including XLSXUse familiar spreadsheet menus for easier migrationWork with a free, lightweight Office suite
microsoft office alternative - wps office

Frequently Asked Questions

How do I create names such as Monday 16-3-20 from dates?

Enter a date or a formula that produces a date in column A, then use Home > Number Format or Format Cells to display the weekday and date. Widen the column so the full name is visible; the macro uses the displayed text.

Why does column C say the new name already exists?

Another sheet or chart tab already has that name. Change the proposed name in column A to a unique value. For a swap, first rename one tab to a temporary unique name.

Why can't the macro rename a worksheet?

Check the message in column C and confirm that column B matches an existing worksheet tab. If workbook structure protection is enabled, use Review > Protect Workbook to remove it before running the macro.

How do I keep the macro when I save the workbook?

Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). When reopening a trusted workbook, enable macros if Excel prompts you.

Can the macro rename the SheetTitles list tab?

No. The macro skips SheetTitles so it can find the list on later runs. Rename that tab only if you also update its name in the VBA code.