Create a ‘Live’ Excel Contents Page

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.

1. Create a new sheet (call it Contents for example)
2. Right Click on the sheet tab name  and select View Code
3. Copy and Paste the code that I’ve added at the end of this post
4. Close the Screen

DONE

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
Range(“B10”).Select

 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
Next c

Set rCell = Nothing

End Sub