How To Create A Link To A Sheet In Excel

Author: Oscar Cronquist Article last updated on October 04, 2021

The macro demonstrated above creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly navigate to a particular worksheet, this is more useful if you have plenty of worksheets in your workbook.

Create links to all sheets in a workbook
1. Video
2. VBA code
3. Explaining VBA code
4. Where to put the code?
5. How to run macro
6. Get Excel file
Create links to all visible worksheets in a workbook
1. Get Excel file
Create links to all hidden worksheets in a workbook
1. Get Excel file

1. Create links to all sheets in a workbook

The macro inserts hyperlinks to all worksheets in your workbook except the current worksheet.

1.1 Video

Watch a video where I explain how to use the macro

1.2 VBA code

Sub CreateLinksToAllSheets() Dim sh As Worksheet Dim cell As Range For Each sh In ActiveWorkbook.Worksheets     If ActiveSheet.Name <> sh.Name Then         ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _         "'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name         ActiveCell.Offset(1, 0).Select     End If Next sh End Sub

1.3 Explaining vba code

Sub CreateLinksToAllSheets()

All macros start withSub and end withEnd Sub.

Dim sh As Worksheet

This line declares sh as a worksheet object.

Dim cell As Range

Cell is declared a range object. A range object can contain a single cell or multiple cells.

For Each sh In ActiveWorkbook.Worksheets

Each worksheet in active workbook is stored in sh, one by one.

If ActiveSheet.Name &lt;&gt; sh.Name Then

This If ... then line avoids linking to active worksheet.

ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ sh.Name &amp; "!A1", TextToDisplay:=sh.Name

Create a hyperlink to current worksheet sh in active cell.

ActiveCell.Offset(1, 0).Select

Select next cell below active cell.

End If

Next sh

Go back to the "For each" statement and store next worksheet in sh worksheet object.

End Sub

Stop macro
Back to top

1.4 Where to put the code?

Press Alt+F11 to open the Visual Basic Editor (VBE).
Press with the right mouse button on your workbook name in the project explorer, see image above.
A context menu appears.
Press "Insert" and another popup menu appears.
Press "Module" to insert a new module.
Copy and paste the VBA macro code above to the newly created code module in your workbook.

1.5 How to run the macro

Select a cell where you want to begin inserting hyperlinks.
Press Alt + F8 to view macros.
SelectCreateLinksToAllSheetsand then press with left mouse button on the "Run" button.

1.6 Get Excel file

2. Create links to all visible worksheets in a workbook

Create links to all visible worksheets programmatically

The image above shows the output of a macro named CreateLinksToAllVisibleSheets.There are two hidden worksheets in this workbook: "hidden" and "Annual report 2011".

The macro returns only hyperlinks of visible worksheets in column A, see the image above.

'Name macro Sub CreateLinksToAllVisibleSheets()  'Dimension variables and declare data types Dim sh As Worksheet  'Go through all worksheets in the current workbook For Each sh In ActiveWorkbook.Worksheets      'Check if current worksheet name is not equal to the worksheet in variable sh AND is visible     If ActiveSheet.Name <> sh.Name And sh.Visible = True Then          'Create hyperlink in the selected cell         ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _         "'" & sh.Name & "'" &  "!A1", TextToDisplay:=sh.Name          'Select next cell below         ActiveCell.Offset(1, 0).Select     End If  'Continue with next worksheet Next sh End Sub

Where to put the code?

How to run the macro?

2.1 Get Excel file

3. Create links to all hidden worksheets in a workbook

Create links to all hidden worksheets

The image above shows a workbook with two hidden worksheets "hidden" and "Annual report 2011". The macro created two hyperlinks in cells A2:A3.

The hyperlinks don't work, the worksheets are hidden and you can't navigate to those worksheets unless you unhide them.

'Name macro Sub CreateLinksToAllHiddenSheets()  'Dimension variables and declare data types Dim sh As Worksheet  'Go through all worksheets in the current workbook For Each sh In ActiveWorkbook.Worksheets      'Check if current worksheet name is not equal to the worksheet in variable sh AND is not visible     If ActiveSheet.Name <> sh.Name And sh.Visible = False Then          'Create hyperlink in the selected cell         ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _         "'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name          'Select next cell below         ActiveCell.Offset(1, 0).Select     End If  'Continue with next worksheet Next sh End Sub

Where to put the code?

How to run the macro?

3.1 Get Excel file