Rename an Excel Worksheet and Update Its Hyperlink with VBA
Question details
Rename a worksheet with VBA and update the corresponding hyperlink target and displayed text on a Main sheet.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook has a Main sheet containing navigation hyperlinks labeled with worksheet names.
- Observed behavior
- The macro should store the original worksheet name, assign a new name, and update matching hyperlinks so they display the new name and point to cell A1 on the renamed sheet.
Confirm that the workbook contains a sheet named Main and that its navigation links are inserted hyperlinks, not HYPERLINK formulas. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), and enable macros only if you trust the code and file.
Use VBA to rename the active worksheet and update matching Main-sheet hyperlinks
Use this macro when each navigation hyperlink on Main displays the exact name of its destination worksheet.
The macro stores the old worksheet name before renaming the sheet. It then finds matching hyperlink labels on Main and updates both SubAddress and TextToDisplay, escaping apostrophes in the new worksheet name.
In desktop Excel, open the target workbook and select Developer > Visual Basic. In the VBA editor, select your workbook in Project Explorer, then choose Insert > Module. If Developer is hidden, enable it in Excel's ribbon customization settings.
Paste this code into the module in the workbook you want to modify: Option Explicit Sub RenameSheetAndUpdateHyperlink() Dim ws As Worksheet Dim mainSheet As Worksheet Dim link As Hyperlink Dim oldName As String Dim newName As String Dim updated As Long Dim renamed As Boolean On Error GoTo Failed If Not TypeOf ActiveSheet Is Worksheet Then MsgBox "Select a worksheet first.", vbExclamation Exit Sub End If Set ws = ActiveSheet If Not (ws.Parent Is ThisWorkbook) Then MsgBox "Select a worksheet in the workbook containing this macro.", vbExclamation Exit Sub End If Set mainSheet = ThisWorkbook.Worksheets("Main") If ws Is mainSheet Then MsgBox "Select the destination worksheet, not Main.", vbExclamation Exit Sub End If oldName = ws.Name newName = InputBox("Enter the new worksheet name:", _ "Rename Worksheet", oldName) If Len(newName) = 0 Then Exit Sub If StrComp(newName, oldName, vbBinaryCompare) = 0 Then Exit Sub ws.Name = newName renamed = True For Each link In mainSheet.Hyperlinks If StrComp(link.TextToDisplay, oldName, vbTextCompare) = 0 Then link.Address = "" link.SubAddress = "'" & Replace(ws.Name, "'", "''") & "'!A1" link.TextToDisplay = ws.Name updated = updated + 1 End If Next link MsgBox "Worksheet renamed to " & ws.Name & ". " & _ updated & " hyperlink(s) updated.", vbInformation Exit Sub Failed: If renamed Then MsgBox "The worksheet was renamed, but hyperlink updates may be incomplete. " & _ "Check the links on Main. Details: " & Err.Description, vbExclamation Else MsgBox "The worksheet was not renamed. Check that Main exists, " & _ "the workbook structure is unprotected, and the new name is valid " & _ "and not already in use. Details: " & Err.Description, vbExclamation End If End Sub
Return to Excel and select the worksheet you want to rename. Choose Developer > Macros, select RenameSheetAndUpdateHyperlink, and click Run. Enter the new sheet name and click OK.
Open Main and find the hyperlink labeled with the new worksheet name. Click it to confirm that it opens cell A1 on the renamed worksheet, then save the .xlsm workbook.
Work with Excel-Compatible Spreadsheets in WPS Office
For everyday spreadsheet editing and navigation, WPS Office offers a free, lightweight alternative with a familiar interface and Excel-format compatibility for a smooth transition. It does not fix this Microsoft Excel VBA workflow; VBA availability depends on your WPS edition and environment, so verify support before relying on the macro.

Frequently Asked Questions
Why does the hyperlink target need quotes around the worksheet name?
An internal worksheet target uses the form 'Sheet Name'!A1. Quoting the sheet name supports spaces and other characters. If the name contains an apostrophe, the macro doubles it with Replace(ws.Name, "'", "''") before assigning Hyperlink.SubAddress.
What if the worksheet is renamed but no hyperlink is updated?
On Main, check the link's displayed text. It must match the old worksheet name exactly apart from letter case; extra spaces or a custom label prevent a match. This macro uses displayed text to identify links, not their existing destination.
Can this macro update a HYPERLINK formula?
Not as written. Select the link cell and check the formula bar. If it contains =HYPERLINK(...), update the formula or the cells it references instead of relying on this macro's Hyperlinks loop.
Why does Excel reject the new worksheet name?
Check that the name is not already used by another sheet, is no longer than 31 characters, and contains none of : \ / ? * [ ]. Names also cannot begin or end with an apostrophe, and History is reserved. If workbook structure is protected, use Review > Protect Workbook to remove that protection with the appropriate password before renaming.
Can I run this macro in Excel for the web?
Excel for the web cannot run VBA macros. Open the workbook in desktop Excel, save it as .xlsm, and run the code through Developer > Macros.




