How to create a sheet index with hyperlinks to all sheets using VBA?

Creating a sheet index with hyperlinks to all sheets in an Excel workbook using VBA can be quite handy, especially for workbooks with a large number of sheets. Below is a step-by-step guide to creating a macro that generates an index sheet with hyperlinks to all the other sheets in the workbook.

Step-by-Step Guide

   Sub CreateSheetIndex()
       Dim wsIndex As Worksheet
       Dim ws As Worksheet
       Dim i As Integer
       Dim startRow As Integer
       
       ' Specify the starting row for the index
       startRow = 1

       ' Check if "Index" sheet already exists and delete it
       On Error Resume Next
       Set wsIndex = ThisWorkbook.Sheets("Index")
       Application.DisplayAlerts = False
       If Not wsIndex Is Nothing Then
           wsIndex.Delete
       End If
       Application.DisplayAlerts = True
       On Error GoTo 0

       ' Add a new worksheet and name it "Index"
       Set wsIndex = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
       wsIndex.Name = "Index"

       ' Add titles to the index sheet
       wsIndex.Cells(startRow, 1).Value = "Sheet Name"
       wsIndex.Cells(startRow, 2).Value = "Hyperlink"

       ' Loop through each worksheet and add a hyperlink to the index sheet
       i = startRow + 1
       For Each ws In ThisWorkbook.Worksheets
           If ws.Name <> wsIndex.Name Then
               wsIndex.Cells(i, 1).Value = ws.Name
               wsIndex.Hyperlinks.Add _
                   Anchor:=wsIndex.Cells(i, 2), _
                   Address:="", _
                   SubAddress:="'" & ws.Name & "'!A1", _
                   TextToDisplay:="Go to " & ws.Name
               i = i + 1
           End If
       Next ws

       ' Autofit the columns
       wsIndex.Columns("A:B").AutoFit

       ' Select the index sheet
       wsIndex.Activate
   End Sub

Open the Visual Basic for Applications (VBA) Editor:

Press `ALT + F11` to open the VBA Editor.

Insert a New Module:

In the VBA editor, right-click on any of the items for your workbook, then go to `Insert` > `Module` to create a new module.

Write the VBA Code:

Copy and paste the following VBA code into the new module:

Run the Macro:

Press `F5` while in the VBA Editor or go back to Excel, press `ALT + F8`, select `CreateSheetIndex`, and click `Run`.

Explanation of the Code

Delete Existing Index Sheet: The script first checks if an index sheet already exists and deletes it to avoid duplicates.
Add New Index Sheet: It creates a new sheet called “Index.”
Loop Through Worksheets: The script loops through all sheets in the workbook, except the new “Index” sheet, and adds each sheet’s name and a hyperlink to cell `A1` of that sheet in the index.
Add Hyperlinks: The `Hyperlinks.Add` method creates a clickable hyperlink that directs to the respective sheet.
Autofit Columns: It adjusts the widths of the columns to fit the content.
Activate Index Sheet: Finally, it selects the “Index” sheet to bring it into view.

This code will function in any Excel workbook and provides a simple,quick way to navigate between sheets.

facebooklinkedin