home

-- Excel - Create Index Page
Author Nigel Rivett
Rename the first worksheet  to “Index” (right click on the tab at the bottom and select rename).
Rename the other two worksheets to Test1 and Test2.
Put the focus back to the index worksheet
Right click on the tab at the bottom again and select view code
In the code window 
Select worksheet from the left drop down menu.
Select Activate from the right drop down menu.

In the worksheet.activate module type

Private Sub Worksheet_Activate()
Dim ws As Worksheet
Dim iRow As Long

    iRow = 1
        
    Me.Columns(1).ClearContents
    Me.Cells(1, 1) = "INDEX"
    
    For Each ws In Worksheets
        If ws.Name <> Me.Name Then
            ws.Range("A1").Name = "Start" & ws.Index
            iRow = iRow + 1
            Me.Hyperlinks.Add Anchor:=Me.Cells(iRow, 1), Address:="", SubAddress:="Start" & ws.Index, TextToDisplay:=ws.Name
        End If
    Next ws
End Sub
Click save
Close the VBScript window to return to the spreadsheet

Now select another worksheet and then select the index worksheet again.
The links to the other worksheets should now appear as the above module was executed when the worksheet was activated.
We now have an index to the other worksheets and hyperlinks to them.
This will be updated whenever the Index worksheet is displayed (and macros are enabled)

Not so useful in the early stages but invaluable when there are a lot of worksheets.


home