Creating The Workbook's Index With Vba Codes
In the index page , hyperlinks are created to all pages of the workbook. Clicking the hyperlinks,can be went to related address.
When "ESC key" is pressed, can be returned to the index page.Index page is automatically updated when a new sheet is added.
.
If you add the following codes to the which page ,index is created in this page :
Private Sub Worksheet_Activate()
Dim Page As Worksheet
Dim k, m As Integer
k = 1
m = 0
Sheets("INDEX").Range("A10:B2000").ClearContents
For Each Page In Worksheets
Sheets("INDEX").Cells(k, 2).Select
Sheets("INDEX").Cells(k, 1).Value = m
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Page.Name & "!A1", TextToDisplay:=Page.Name
k = k + 1
m = m + 1
Next Page
Sheets("INDEX").Cells(1, 1).Select
End Sub
