To create an internal hyperlink to a cell or range with Excel VBA, use the destination in the hyperlink’s SubAddress argument and leave Address blank. For example, a link can take a reader from an index sheet to cell A1 on a results sheet; the code below shows a reusable version.
What “link to a range” means in Excel
The phrase can refer to two different things. Most often, it means a clickable hyperlink that moves the user to a cell in the same workbook or in another file. In VBA, that is created with the Hyperlinks.Add method. The link’s anchor is the cell the user clicks, while its destination is specified separately.
It can also mean storing a range in a VBA variable so code can read or change those cells. That is done with a Range object and the Set statement; it does not create a clickable link. For example, Set target = Worksheets("Data").Range("A1:C10") gives the variable target a reference to that range. If the goal is navigation for a workbook user, create a hyperlink. If the goal is for a macro to work with cells, use a range variable.
An internal hyperlink uses a sheet-and-cell reference in SubAddress. An external link uses a file path or web address in Address, and can optionally use SubAddress to identify a location inside the destination workbook. The distinction matters: putting a web URL into SubAddress will not create a normal web link.
Create an internal hyperlink with VBA
This example puts a clickable label in A2 on the Index sheet. Clicking it navigates to A1 on a sheet named Quarterly Results. The destination is held in a range variable so the sheet and cell can be changed without rebuilding the hyperlink string by hand.
Sub AddInternalRangeLink()
Dim ws As Worksheet
Dim linkCell As Range
Dim target As Range
Dim subAddress As String
' Save a copy before running: VBA changes cannot be undone with Ctrl+Z.
Set ws = ThisWorkbook.Worksheets("Index")
Set linkCell = ws.Range("A2")
Set target = ThisWorkbook.Worksheets("Quarterly Results").Range("A1")
' Quote the sheet name and escape any apostrophe in its name.
subAddress = "'" & Replace(target.Worksheet.Name, "'", "''") & "'!" & target.Address
' Replace an existing hyperlink in the anchor cell, if present.
If linkCell.Hyperlinks.Count > 0 Then linkCell.Hyperlinks.Delete
linkCell.Hyperlinks.Add Anchor:=linkCell, Address:="", _
SubAddress:=subAddress, TextToDisplay:="Open quarterly results"
End Sub
The code assumes both worksheets are in the workbook containing the macro, because it uses ThisWorkbook. The link appears in Index!A2, and its destination is Quarterly Results!A1. The single quotes around the sheet name are important when a sheet name contains spaces; the code also doubles an apostrophe inside a sheet name so Excel can parse it. TextToDisplay sets the visible text in the anchor cell.
A range can be used for the destination instead of one cell. For example, changing the target to Range("B3:F12") makes the hyperlink refer to that range. In normal navigation, Excel activates the destination and generally brings the range’s upper-left cell into view; do not rely on a hyperlink to select and visibly outline every cell in a multi-cell range.
Run the macro in Excel for Microsoft 365
- Save a copy of the workbook before running the macro. Excel macros cannot be reversed with Ctrl+Z.
- In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. On a Mac, Option+F11 may open it; some keyboards require Fn as well. Excel for the web does not create or run VBA macros.
- In the editor, choose Insert > Module, then paste the procedure into the module window.
- Return to Excel and run
AddInternalRangeLinkfrom the Macro dialog or from the Visual Basic Editor. If prompted, enable macros only for a workbook and source you trust. - Check that the Index and Quarterly Results sheet names match exactly, then click the new link in A2.
If the workbook is saved as an ordinary .xlsx file, VBA code cannot be retained in it. Save a macro-enabled copy as .xlsm if the procedure needs to remain in the workbook. The hyperlink itself can remain in a workbook without VBA after the macro has created it. On Mac, the Visual Basic Editor and VBA support are available in the desktop Excel app, though keyboard shortcuts and some security prompts can differ from Windows.
Choose the right destination reference
For a fixed destination, a direct cell reference is straightforward. A named range can be easier to understand when the destination has a business meaning, such as a named cell called ExecutiveSummary. A sheet-and-cell address is often simplest for a small navigation menu, while a name can be more maintainable when the workbook layout changes.
- One cell: use a reference such as
Range("A1")when the reader should jump to a specific starting point. - A block of cells: use a multi-cell reference such as
Range("B3:F12")when it is useful to bring a table or section into view. - A named range: use a defined name when the destination should be identified by purpose rather than by its current coordinates. Confirm whether the name is workbook-scoped or worksheet-scoped.
- A changing destination: calculate or look up the target range in VBA first, then build the hyperlink’s
SubAddressfrom that range.
Using a range variable does not make the link dynamic by itself. If rows are inserted or the destination moves, a hard-coded address may no longer represent the intended location. A named range or code that identifies the target by a stable label can be more resilient, but it should still be checked after workbook structure changes.
Build links for changing targets
For a menu with many destinations, avoid copying a separate procedure for every row. Put the destination sheet name or a stable identifier in a table, then have a macro read the selected row and construct the link. The link anchor can be the current row’s cell, and the target can be found with Worksheets and Range. Validate the sheet name and target before calling Hyperlinks.Add; otherwise, a typo can stop the macro with a runtime error.
A practical pattern is to keep the navigation labels and targets together, for example a visible label in column A and a destination address in column B. The macro can use the label as TextToDisplay and create a link in that row. This makes the workbook easier to maintain than burying every destination in code. If users can edit the table, consider checking that each referenced sheet exists and that the destination is not blank before creating a link.
To make a link point to the active cell on another sheet, first set a target variable to the desired cell and then use that variable’s worksheet name and address when forming SubAddress. Avoid using Selection as the only source of the destination unless the macro is specifically meant to follow a user’s current selection. Explicit references are easier to review and less likely to point somewhere unexpected after the user clicks elsewhere.
Link to another workbook or a web page
For an external workbook, set Address to the workbook path. If the target file contains a particular sheet or cell, use SubAddress for that internal location. For a web page, set Address to its URL and leave SubAddress empty unless the destination uses a page fragment or another supported location. The following short procedure creates a link to a web page:
Sub AddWebLink()
Dim ws As Worksheet
Dim linkCell As Range
' Save a copy before running: VBA changes cannot be undone with Ctrl+Z.
Set ws = ThisWorkbook.Worksheets("Index")
Set linkCell = ws.Range("A3")
If linkCell.Hyperlinks.Count > 0 Then linkCell.Hyperlinks.Delete
linkCell.Hyperlinks.Add Anchor:=linkCell, _
Address:="https://www.microsoft.com/", _
TextToDisplay:="Visit Microsoft"
End Sub
Paths to workbooks require thought about where the file will live. A local path such as a user’s Downloads folder may work only on that person’s computer. A shared network or cloud location can be more appropriate for a team, but permissions and link formats affect whether other users can open it. Test external links from the same location and account that the intended readers will use.
Common problems and fixes
- The link does nothing or goes to the wrong sheet: check the sheet name, punctuation, and cell address in
SubAddress. A sheet name with spaces should be enclosed in single quotes in the reference. - Run-time error: subscript out of range: a worksheet name in the VBA reference probably does not exactly match a tab name, or the code is looking in the wrong workbook. Check capitalization, spaces, and whether the sheet is in
ThisWorkbookor another workbook. - The link text is not what you expected:
TextToDisplaycontrols what appears in the anchor cell. It can replace the cell’s existing displayed text, so use the desired label deliberately. - The link works on one computer but not another: the external workbook address may refer to a local or unavailable path. Use a location accessible to the recipients and verify their permissions.
- The macro is unavailable: confirm the file is saved as .xlsm, macros are permitted by the organization’s security settings, and the code is in a standard module if you intend to run it from the Macro dialog.
One common misconception is that creating a hyperlink to a range is the same as creating a live link to the range’s values. It is not. A hyperlink navigates to a location. To display or calculate values from another sheet or workbook, use worksheet formulas or VBA that reads and writes values instead. Similarly, the Range object is a VBA reference to cells, not a hyperlink and not a formula.
Hyperlinks, range variables, and alternatives
Use a hyperlink when the workbook needs clickable navigation, such as a contents sheet, a dashboard button, or a return-to-index link. Use a range variable when the macro needs to format, inspect, copy, or update cells. A procedure may use both: the range variable identifies the anchor or destination, and Hyperlinks.Add creates the navigation action.
For a small workbook, you may not need VBA at all. Use Insert > Link in desktop Excel, or press Ctrl+K on Windows (Command+K on Mac), then choose a place in the document if that option is available in the dialog. Excel also supports the HYPERLINK worksheet function for links created by formulas. Manual links and formulas are often easier to audit; VBA is useful when links must be generated or refreshed repeatedly. Keep in mind that VBA cannot run in Excel for the web, so a macro-driven link-generation workflow needs desktop Excel or a non-VBA alternative.
Choose the least complicated method that fits the workbook. If a few links are fixed, manual hyperlinks or formulas may be sufficient. If dozens of links are created from changing data, VBA can automate the work, provided the workbook is macro-enabled and users can run macros under their security policy. Regardless of the method, test both the clickable label and its destination after renaming sheets, moving files, or restructuring the workbook.
Frequently Asked Questions
How do I link to a cell on another sheet in Excel VBA?
Use Hyperlinks.Add with the clickable cell as Anchor, an empty Address, and a sheet-and-cell reference in SubAddress. Quote sheet names that contain spaces.
Can a VBA hyperlink point to a named range?
Yes. A defined name can be used as the destination in SubAddress. Check the name’s scope and spelling, especially if a sheet-level name shares its name with another defined name.
Does a link to a range select every cell in that range?
Not reliably. It navigates to the destination location, usually bringing the range’s upper-left cell into view. If a macro must select, format, or process all cells, use the destination as a VBA Range object and perform that action explicitly.
Can I create these links in Excel for the web?
Excel for the web does not run VBA macros. A link already created by desktop Excel can still be useful in a workbook, but use desktop Excel to run the VBA procedure.
Why does my internal link open the wrong place?
Review the sheet name and the exact target reference in SubAddress, including quotes and punctuation. Also confirm that the macro refers to the workbook that actually contains the destination sheet.
Related
- Excel Text to Columns: Split at the First Delimiter
- Excel Text to Columns: Use a Line Break Delimiter
- Excel Text to Columns: Split at the Last Space
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro Will Not Run? Fix the Common CausesIf an Excel macro will not run, first check that the workbook is saved as a macro-enabled file,…
- VBA Delete Current Row: Safe Methods and ExamplesTo delete the current row in Excel with VBA, delete the entire worksheet row that contains the active…
- Excel VBA: Copy a Worksheet and Rename ItTo copy a worksheet and rename the copy with VBA, use the worksheet’s Copy method, then set the…
- Excel Goal Seek VBA Code for a Whole ColumnTo run Goal Seek down a column, loop through the populated rows and call Range.GoalSeek once per row:…
- MS Access VBA: Send Email With an Attachment SafelyYes. In Microsoft Access for Windows, VBA can create an email in classic Outlook, add a file with…
- Excel VBA Progress Bar: How to Build a Responsive OneAn Excel VBA progress bar shows how much of a long-running macro has finished. For a visual bar,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.