Rename Excel Sheet Tabs from a Worksheet List with VBA
Question details
Rename Excel worksheet tabs using old and new names stored in a worksheet list.

- 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.
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.
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.
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.
In Excel, enable Developer through File > Options > Customize Ribbon if needed. Select Developer > Visual Basic, then choose Insert > Module.
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
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.
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.

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.

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.




