Auto-Generate an Index Using VBA

An alternative is to use VBA to auto-generate the index. The following code will automatically create a clickable, hyperlinked index of all the sheets you have in the workbook. The index is re-created each time the sheet that houses the code is activated.

This code should live in the private module for the Sheet object. Insert a new worksheet into your workbook and name it something appropriate-Index, for instance. Right-click the index sheet's tab and select ViewCode from the context menu or select Alt/Option-F11.

Enter the following Visual Basic code:

 Private Sub Worksheet_Activate( )
 Dim wSheet As Worksheet
 Dim l As Long
 l = 1
   With Me
   .Columns(1).ClearContents
   .Cells(1, 1) = "INDEX"
   .Cells(1, 1).Name = "Index"
   End With

   For Each wSheet In Worksheets
   If wSheet.Name <> Me.Name Then
   l = l + 1
   With wSheet
   .Range("A1").Name = "Start" & wSheet.Index
   .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:= _
   "Index", TextToDisplay:="Back to Index"
   End With
   Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
   SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
   End If
   Next wSheet
 End Sub

Press Alt-Q to get back to your workbook and then save your changes. To make the code run, you will first need to deactivate your worksheet (select another sheet) and select the index sheet.

Notice that in the same way you name a range of cells, the code names cell A1 on each sheet Start, plus a unique whole number representing the index number of the sheet. This ensures that A1 on each sheet has a different name. If A1 on your worksheet already has a name, you should consider changing any mention of A1 in the code to something more suitable-an unused cell anywhere on the sheet, for instance.

Be aware that if you select the Office button → Prepare → Properties → Document Properties → Advanced Properties ([pre-2007, File → Properties → Summary) and enter a URL as a hyperlink base, the index created from the preceding code might not work. A hyperlink base is a path or URL that you want to use for all hyperlinks with the same base address that are inserted in the current document.