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

    create live excel contents page image 2

    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

    Free Download