VBA & Macro Problems

Rename an Excel Worksheet and Update Its Hyperlink with VBA

John WilsonJohn Wilson Nov 13, 2026 868 views

Question details

Rename a worksheet with VBA and update the corresponding hyperlink target and displayed text on a Main sheet.

Rename an Excel Worksheet and Update Its Hyperlink with VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Insert a standard VBA module

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.

2
Paste the rename-and-update macro

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

3
Run the macro from the destination worksheet

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.

4
Verify the updated navigation link

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.

Exact labels and valid worksheet names are required: The macro updates all hyperlinks whose displayed text matches the old sheet name, ignoring letter case. It does not rewrite HYPERLINK formulas. Worksheet names must be unique, no longer than 31 characters, and cannot contain : \ / ? * [ ]. If no hyperlink label matches, the sheet is still renamed but the update count is zero.
Free Microsoft Office alternative

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.

Work with Microsoft Excel .xlsx and .xls formats.Organize worksheets and use spreadsheet hyperlinks for workbook navigation.Use a familiar spreadsheet interface for an easy transition from Excel.Handle everyday spreadsheet tasks with a free, lightweight Office suite.
microsoft office alternative - wps office

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.