This post is about "How to Generate an Index of Excel Sheets Dynamically" which is very helpful when you are dealing with Large excel files with lots of sheets/tabs.
Step 1. Create a New Sheet called "Index"
Step 2. Right click on the sheet and go to View Code ( It will open VB Editor )
Step 3. Copy the following VB Script there
Private Sub Worksheet_Activate() Dim wSheet As Worksheet Dim l As Long l = 1 With Me .Columns(1).ClearContents .Cells(1, 1) = "INDEX" .Cells(1, 1).Name = "Index" End With For Each wSheet In Worksheets If wSheet.Name <> Me.Name Then l = l + 1 With wSheet .Range("A1").Name = "Start_" & wSheet.Index .Hyperlinks.Add Anchor:=.Range("C1"), Address:="", _ SubAddress:="Index", TextToDisplay:="Back to Index" End With Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _ SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name End If Next wSheet End Sub
Step 4. Now save and close the VB Editor
Step 5. Go to a different sheet and come back to Index Sheet
Step 6. Now you can see that the Index has been generated along with the list of sheets with links
Step 7. when you go to a sheet you can see another link to go back to index sheet as well.