Author: Oscar Cronquist Article last updated on January 11, 2019

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. Copy and paste the VBA macro below to a code mode in your workbook. More detailed instructions below.
  2. Select a cell where you want to begin inserting hyperlinks.
  3. Press Alt + F8 to view macros.
  4. Select CreateLinksToAllSheets and then click on Run button.

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

Watch a video where I explain how to use the macro

https://youtu.be/Yms59nxKvNY

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

Explaining vba code

Step 1 - Open VBA Editor

You write macros in the Visual Basic Editor. Open the Visual Basic Editor using one of these instructions:

  • Press Alt+F11
  • Go to tab Developer and click Visual basic "button"

Step 2 - Create a module

A macro procedure is stored in a code module. Letยดs create a module.

  1. Right click on your workbook in the project explorer.
  2. Click Insert | Module.

Step 3 - Write macro

Click in code module window. Here you write 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 <> sh.Name Then

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

ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
sh.Name & "!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

Download Excel file


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

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