VBA & Macro Problems

How to Fix Excel VBA Hyperlinks to Worksheets Using a Name

Maira Mehtab Sep 28, 2026 868 views

Question details

The user needs to create hyperlinks in a main worksheet that link to other worksheets based on their names using Excel VBA, but the current hyperlink addresses are invalid.

Product: Excel
Device & OS: not provided
Scenario: Using Excel VBA to dynamically generate hyperlinks in a column that navigate to corresponding worksheets based on cell text.
Observed behavior: The generated hyperlinks are invalid and fail to navigate to the target worksheet, typically returning a reference error.

Before you start

Before editing your VBA code, verify that the names listed in your main worksheet exactly match the actual names of the target worksheets, including any spaces or special characters.

Solution 1Recommended

Format the Hyperlink SubAddress with Single Quotes

Use single quotes around the worksheet name in your VBA code to handle spaces and special characters correctly.

When a worksheet name contains spaces or specific special characters, Excel requires the worksheet name to be enclosed in single quotes within the hyperlink reference. If your VBA script omits these quotes, the resulting hyperlink will fail and display an invalid reference error.

Open the VBA Editor

Press ALT + F11 in Excel to open the Visual Basic for Applications (VBA) Editor.

Locate the Hyperlink Code

Find the module or script where the 'Hyperlinks.Add' method is being used to generate the links.

Update the SubAddress Property

Modify the SubAddress string to include single quotes around the worksheet name variable. For example, use the syntax: SubAddress:="'" & lName & "'!A1" (assuming lName is your worksheet name variable).

Test the Macro

Run the macro again to regenerate the hyperlinks. Click on the newly created links to ensure they successfully navigate to the target worksheet.

Target Cell Reference: Always include a target cell reference, such as '!A1', at the end of the subaddress string. This ensures the hyperlink lands on a specific, valid cell within the destination worksheet.

Write and Execute VBA Code Seamlessly in WPS Office

WPS Office Spreadsheet provides excellent compatibility with Excel macros and VBA scripts. You can write, edit, and run your hyperlink generation scripts using the built-in Developer tools without compatibility issues.

1. Open WPS Spreadsheet: Launch WPS Spreadsheet and open your macro-enabled workbook.
2. Access Developer Tools: Navigate to the Developer tab on the top ribbon.
3. Open the VBA Editor: Click on 'Macros' or press ALT + F11 to open the VBA Editor.
4. Execute Your Script: Paste your corrected hyperlink code with the single quotes included, then run the script to generate valid links.

Fully compatible with Microsoft Excel .xlsm and .xlsb formatsBuilt-in Developer tab for macro editing and executionLightweight, fast, and free alternative for complex spreadsheet tasksSeamless migration of your existing VBA scripts and modules

microsoft office alternative - wps office

Frequently Asked Questions

Why do my Excel VBA hyperlinks say 'Reference isn't valid'?

This error most commonly occurs when your macro links to a worksheet name that contains spaces, but the code fails to wrap the sheet name in single quotes within the SubAddress parameter.

How do I link to a specific cell in another worksheet using VBA?

In the SubAddress property of the Hyperlinks.Add method, append the cell reference immediately after the worksheet name and an exclamation mark. For example: "'Sheet2'!B5".

Do I need single quotes if my worksheet name has no spaces?

While it is not strictly required by Excel for one-word names, it is highly recommended as a best practice. Adding single quotes prevents errors if the sheet name is ever renamed to include a space later.

Can I loop through column A to create hyperlinks for all listed sheets?

Yes, you can use a 'For Each' loop in VBA to iterate through the cells in column A, read the text value representing the sheet name, and dynamically generate a hyperlink for each row using ActiveSheet.Hyperlinks.Add.

Excel & Data Problems