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

Related posts:

Quickly create links to sheets, tables, pivot tables and named ranges in a workbook

Excel vba: Select cell A1 on all sheets before you close a workbook

Quickly create new sheets (vba)

Excel 2007 vba: Create a custom-made item on the shortcut menu

List all open workbooks and corresponding sheets (vba)