VBA Code to Link Excel Worksheets

VBA & macros · Updated November 21, 2026

To link to a worksheet in the same Excel workbook, use VBA’s Hyperlinks.Add method with an empty Address and the destination sheet and cell in SubAddress. The example below adds a clickable link in Index!A2 that opens Summary!A1.

What “link a worksheet” can mean

In Excel, “link a worksheet” can refer to two different tasks. A navigation link is clickable text or a cell that takes someone to another sheet. A data link is a formula that displays a value stored on another sheet and updates when that value changes. VBA can create either, but the code and result are different.

The macro here creates a navigation hyperlink. It is useful for an index, a dashboard, or a workbook with many sheets. If you want one cell to reflect another cell’s value, use a cross-sheet formula instead; that distinction is covered below.

VBA code for an internal worksheet link

This macro puts a link in cell A2 of a sheet named Index. Clicking it takes the user to cell A1 of a sheet named Summary. Change those names and cell addresses to suit your workbook.

Sub AddSummaryWorksheetLink()
    Dim sourceCell As Range
    Dim targetSheet As Worksheet
    Dim subAddress As String

    ' The destination sheet must exist in this workbook.
    Set targetSheet = ThisWorkbook.Worksheets("Summary")
    Set sourceCell = ThisWorkbook.Worksheets("Index").Range("A2")

    ' Quote the sheet name so names with spaces are supported.
    subAddress = "'" & Replace(targetSheet.Name, "'", "''") & "'!A1"

    ' Replace any existing hyperlink in the source cell.
    sourceCell.Hyperlinks.Delete
    sourceCell.Hyperlinks.Add _
        Anchor:=sourceCell, _
        Address:="", _
        SubAddress:=subAddress, _
        TextToDisplay:="Open Summary", _
        ScreenTip:="Go to the Summary sheet"
End Sub

ThisWorkbook means the workbook that contains the VBA code. That is usually what you want when the macro is stored in the workbook it edits. If the macro is stored in another workbook, such as Personal.xlsb, ThisWorkbook refers to that other workbook; you would need to deliberately identify the workbook containing the sheets instead.

The macro expects both named sheets to exist. If either name is missing or has a spelling difference, VBA stops with a subscript error. It also replaces the hyperlink in A2, if there is one, and sets the displayed text to “Open Summary.” Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z.

Where to put and run the macro

In Excel for Microsoft 365 on Windows, open the workbook and press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste the code into the module, and return to Excel. Run it with Alt+F8, select AddSummaryWorksheetLink, and choose Run. You can also run a macro from Developer > Macros.

If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer. On a Mac, open the Visual Basic Editor using the Developer tab or the available Visual Basic command; keyboard shortcuts can differ by keyboard and Excel settings. The macro itself works in desktop Excel for Mac, though the editor layout and steps for opening it differ from Windows.

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the code. A standard .xlsx file does not store VBA macros. When reopening the file, Excel may require you to enable macros, depending on its security settings and the file’s source.

Changing the destination and link text

To link to a different destination, change the source range and the address at the end of subAddress. For example, change Range("A2") to Range("B4") to place the link in B4, or change "'" & Replace(targetSheet.Name, "'", "''") & "'!A1" so the final cell address is A10. Change TextToDisplay to set what appears in the source cell, and change ScreenTip to adjust the text shown when the pointer rests over the link.

The apostrophe marks around the sheet name make the reference work with names that contain spaces, such as “Sales Data.” The Replace expression also doubles any apostrophe inside a sheet name. For example, a sheet named Director's Notes needs the apostrophe escaped in the internal address. Keeping this construction is safer than building an address that assumes every sheet name is a single word.

To link to a cell other than A1, use that cell’s address in the subaddress, such as !D7. The link will navigate to the specified cell, but it does not change the destination cell’s value or create a live data connection.

Creating links for a workbook index

For a small workbook, adding one hyperlink with a macro is usually enough. For a workbook with many worksheets, an index sheet can provide a convenient list of destinations. You can copy the macro and adapt it for each destination, or write a loop that visits each worksheet and adds a link for it. Before automating a list, decide whether to include hidden sheets and where the list should start; otherwise a macro may create links users cannot reach or overwrite existing content.

A simple manual approach is to create the index entries in a dedicated column and run a link-creation macro for each entry. Keep the index sheet separate from the source data, and avoid clearing a whole worksheet merely to refresh the links unless you are certain it contains nothing users need. If sheet names change, links built from fixed text may need to be updated. Code that reads each sheet’s current Name property can reduce that maintenance, but should still handle an index sheet that must not link to itself.

Hyperlinks versus formulas between sheets

A hyperlink moves the selection to another location; a formula brings a value into the current cell. If a dashboard should display the value in a source cell and update when that source changes, use a direct worksheet reference rather than a navigation macro.

With the source value in Sales Data!B2 and the destination cell on another sheet, enter this in the destination cell:

='Sales Data'!B2

The formula returns the current value of B2 on Sales Data. The apostrophes are needed because the sheet name contains a space. For a sheet named Sales, the simpler reference =Sales!B2 is valid. A formula reference does not make a clickable navigation link by itself.

You can also create a clickable link with a worksheet formula. With the destination sheet named Sales Data, enter this formula in the cell where the link should appear:

=HYPERLINK("#'Sales Data'!A1","Open Sales Data")

That formula displays “Open Sales Data” and navigates to A1 on the named sheet when clicked. Choose VBA when you need to create or update links through a macro, and choose a formula when a worksheet formula is enough. Both approaches support navigation within the workbook, but a VBA macro requires macros to be allowed and a macro-enabled file to preserve its code.

Links to another workbook

A link to a different workbook needs an external address as well as a destination. In VBA, the Address argument can contain a workbook path, while SubAddress identifies a sheet and cell inside that workbook. The exact path depends on where the file is stored. A link to a file on a local drive may not work for another person whose computer uses a different folder, and a moved or renamed file can break the destination.

For shared workbooks, consider whether the file is stored in a stable network or cloud location and whether intended users have permission to open it. Do not assume that an external hyperlink updates data; it normally opens a location. External data references and links that retrieve values are a separate issue and may prompt users to update links or resolve a changed file path.

Common problems and checks

  • “Subscript out of range”: Check that the names in Worksheets("Summary") and Worksheets("Index") match the visible sheet tabs exactly, including spaces and punctuation.
  • The link opens the wrong place: Confirm the target sheet name and cell in subAddress. The example deliberately points to A1.
  • The macro is missing after saving: Save as .xlsm, not .xlsx, if you want to retain VBA code.
  • The macro will not run: Check Excel’s macro security notification and whether the file is trusted. Do not enable macros in an unknown file just to use its links.
  • The displayed label is unexpected: TextToDisplay sets the text shown in the anchor cell. Adjust it if the macro replaces a label you wanted to keep.
  • A sheet name includes an apostrophe or spaces: Keep the quoted subaddress construction in the sample rather than removing its escaping logic.

If a link looks like plain text, verify that the macro ran and that the destination cell was not later overwritten. You can also right-click a linked cell and inspect or edit its hyperlink. A sheet protected in a way that prevents changes may also stop code from modifying the source cell; review the protection settings rather than removing protection without permission.

Safety and maintenance

Macros can change workbook contents, and their actions are not reversed by Excel’s Undo command. Save a separate copy before running new or edited code, especially if the workbook contains important data. Review code before enabling it, use trusted sources, and avoid running macros from files you did not expect to receive. The sample changes only the specified source cell’s hyperlink and displayed text, but adapting it to loop over sheets or clear a range can have a much wider effect.

When a workbook is shared, tell collaborators that it contains VBA and explain what the macro changes. Users of Excel for the web can open and edit many workbook features, but cannot run VBA macros there; they need desktop Excel to run this code. If recipients only need to click existing hyperlinks, they do not need to run the macro themselves, provided the workbook has been saved with the links already created.

Frequently Asked Questions

Can VBA link directly to a specific cell on another sheet?
Yes. Set SubAddress to the sheet name and cell, such as 'Summary'!D7, and use an empty Address for a location in the same workbook.

Does a worksheet hyperlink update data?
No. It navigates to a location. To display a changing value from another sheet, use a cross-sheet formula such as ='Sales Data'!B2.

Can I use this macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel to run the code; existing hyperlinks can still be useful for navigation.

Will the link still work if I rename the destination sheet?
Excel may adjust existing internal hyperlinks when a sheet is renamed, but code that runs later and refers to the old name will fail. Update the sheet name in the macro or make the code identify the destination another way.

Can I add the link without VBA?
Yes. Use Insert > Link in desktop Excel to add a link to a place in the current document, or enter a HYPERLINK formula. VBA is helpful when link creation needs to be automated or repeated.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.