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