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.

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

Back to top

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

Back to top

1.3 Explaining vba code

Sub CreateLinksToAllSheets()

All macros start with Sub and end with End 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?

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

Back to top

1.5 How to run the macro

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

Back to top

1.6 Get Excel file

Get the Excel file


Create-Links-To-All-Sheets-in-a-Workbook1.xlsm

Back to top

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?

Back to top

2.1 Get Excel file

Get the Excel file


Create-Links-To-All-Visible-Sheets-in-a-Workbook.xlsm

Back to top

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?

Back to top

3.1 Get Excel file

Get the Excel file


Create-Links-To-All-Hidden-Sheets-in-a-Workbook.xlsm

Back to top

Recommended articles

List all open workbooks and corresponding sheets [VBA]

In this post, I am going to demonstrate how to automatically create a new sheet in the current workbook and […]

List all open workbooks and corresponding sheets [VBA]

List Excel defined Tables in a workbook [VBA]

The following macro inserts a new sheet to your workbook and lists all Excel defined Tables and corresponding Table headers […]

List Excel defined Tables in a workbook [VBA]

Related articles

test
Count unique distinct values in a filtered Excel defined Table
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]