A little macro never hurt anyone
So here’s a little macro that can automatically create and update a contents page with hyperlinks in any of your Excel files.
- Create a new sheet (call it Contents for example)
- Right Click on the sheet tab name and select View Code
- Copy and Paste the code that I’ve added at the end of this post
- Close the Screen
Screenshot after you’ve pasted the code
Now whenever you click on this contents sheet the code will run and update your contents list.
REMEMBER TO SAVE YOUR FILE AS .XLSM
(otherwise you will lose your macro)
Here’s the code to copy and paste….
Private Sub Worksheet_Activate()
‘This macro creates a list of sheet names and hyperlinks to cell A1 of those sheets
‘Change this reference if you want the index list to start in a different cell
Dim c, d
Dim rCell As Range
d = 0 ‘counter used to increment rows in offset command
For Each c In Worksheets
Set rCell = ActiveCell.Offset(d, 0)
rCell.Value = c.Name
rCell.Hyperlinks.Add Anchor:=rCell, _
Address:=””, SubAddress:=”‘” & c.Name & “‘” & “!A1”, TextToDisplay:=c.Name
d = d + 1
Set rCell = Nothing