VBA & Macro Problems

Use VBA to Open a Differently Named Worksheet in Excel

Natalie Taylor Sep 30, 2026 869 views

Question details

The user wants to use VBA to open or navigate to a specific target worksheet when interacting with a hyperlink or cell event where the displayed text differs from the actual sheet name.

How to Open a Differently Named Worksheet Using VBA in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Clicking a hyperlink or cell (e.g., labeled "Go To Sheet") to trigger a macro that unhides and opens a differently named target sheet (e.g., "test").
Observed behavior: The user needs a script to correctly identify and open the target sheet regardless of the cell's display text, while ensuring the target sheet is made visible and selected. The code must also run correctly in the proper Excel environment.

Before you start

Ensure that your macro settings are enabled in the Trust Center to allow VBA code to run. Note that VBA scripts only function in the desktop version of Excel and will not execute in Excel for the Web.

Solution 1Recommended

Use Worksheet Events to Unhide and Select Target Sheet

Directly reference the hidden or differently named sheet within a worksheet event (such as a hyperlink click or double-click) to make it visible and activate it.

Since the display text of your trigger cell does not match the actual sheet name, you must explicitly hardcode the target sheet's name into your VBA script. This guarantees Excel always opens the correct destination tab.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor in your Excel desktop application.

Insert Code into the Trigger Sheet

In the Project Explorer pane on the left, double-click the sheet containing your hyperlink or trigger cell (e.g., Sheet1). Enter your event code using the exact target sheet name, such as: Worksheets("test").Visible = True followed by Worksheets("test").Select.

Add Auto-Hide Functionality (Optional)

To automatically hide the sheet when you navigate away, double-click the target sheet's module (e.g., Sheet named "test") in the Project Explorer and add a Worksheet_Deactivate event containing the code: Me.Visible = xlSheetHidden.

Save and Test

Save your workbook as an Excel Macro-Enabled Workbook (.xlsm), return to your spreadsheet, and test the hyperlink or cell click.

Use Worksheet Events to Unhide and Select Target Sheet

Web Compatibility: VBA codes do not work in Excel for the Web. You must open the workbook in the desktop application for the macros to trigger properly.

Advanced Macro Support in WPS

Use WPS Spreadsheet for VBA and Macro Management

WPS Spreadsheet offers excellent compatibility with Microsoft Excel macros and VBA scripts. You can seamlessly run, edit, and manage your VBA code in a familiar and lightweight environment, ensuring your automated workflows operate smoothly.

1. Open your Workbook: Launch WPS Spreadsheet and open your macro-enabled workbook (.xlsm) containing the hyperlink events.
2. Enable Macros: If prompted by the security warning at the top of the worksheet, click 'Enable Macros' to allow your VBA scripts to execute.
3. Access the VBA Editor: Navigate to the 'Tools' tab on the top ribbon and click on 'Developer' or 'Macro' to open the VBA Editor to review or modify your worksheet target names.
4. Run the Event: Return to the spreadsheet and click your 'Go To Sheet' hyperlink. The differently named worksheet will unhide and open automatically.

Fully compatible with Microsoft Excel (.xlsm) formats and VBA syntaxBuilt-in macro editor to easily modify and debug worksheet eventsLightweight application with a familiar, easy-to-use interfaceCost-effective alternative for advanced spreadsheet automation

Frequently Asked Questions

Why does my VBA code to open the worksheet not work in Excel for the web?

VBA (Visual Basic for Applications) is exclusively supported in desktop versions of Excel (Windows and Mac). Excel for the web runs in a browser and lacks the underlying architecture to execute VBA macros. You must open the file in the desktop app to use these features.

How do I hide the target sheet automatically when I switch to another tab?

You can add a Worksheet_Deactivate event in the VBA module of your target sheet. In the VBA editor, double-click the target sheet and use the code `Me.Visible = xlSheetHidden` so the sheet hides itself whenever you click away from it.

Does the displayed hyperlink text need to match the actual sheet name?

No, the displayed text can be anything (e.g., "Go To Sheet"). As long as the underlying VBA code explicitly references the exact target worksheet name (e.g., `Worksheets("test")`), it will successfully open the correct destination sheet.

How do I enable macros if my VBA script isn't running?

Go to File > Options > Trust Center > Trust Center Settings, then select Macro Settings. Choose 'Enable VBA macros' (or 'Disable all macros with notification' and manually click 'Enable Content' when opening the file) and click OK to apply the changes.

Excel & Data Problems