I commonly have large excel spreadsheets with many tabs or worksheets within the same workbook.
I use the following Microsoft excel macro to generate a contents page.
NOTE: I’m assuming the “Contents” worksheet will be the first and the list will be generated from A2 downwards.
Private Sub CommandButton1_Click()
For i = 2 To Sheets.Count
Range("a" & i) = Sheets(i).Name
ActiveSheet.Hyperlinks.Add Anchor:=Range("a" & i), Address:="", SubAddress:="'" & Sheets(i).Name & "'!A1", TextToDisplay:=Sheets(i).Name
Next i
Columns("A:A").EntireColumn.AutoFit
End Sub
by Andy Weir: