The macro demonstrated below creates hyperlinks to all sheets in current workbook. Select a start cell and then run macro CreateLinksToAllSheets().

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 *.xlsm file

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

Recommended articles

List all open workbooks and corresponding sheets (vba)

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

Comments(1) Filed in category: Excel, VBA

List all tables and corresponding headers in a workbook (vba)

This macro creates a new sheet and lists all tables and corresponding table headers in a workbook. Example Sheet1, 2 […]

Comments(1) Filed in category: Excel, VBA