Automatically Create an Index for Your Excel File
Do you want one central location where you can easily navigate to any worksheet in your file, and then navigate back with one click? This macro creates an index that lists all sheets in your workbook. The best part is that the index excel macro updates itself everytime you select the index sheet.
If you need an index sheet in your file, you probably already have a zillion worksheets in your file, but add one more, and call it “Index”, or whatever you want to identify it as an index (table of contents, etc.). Next, right click the Index tab and select ‘View Code’. This is where you will enter the code below. Click on another sheet in your file, then click back on your Index sheet. You’ll notice that it has populated a list of all the sheets in your file, complete with a convenient link to them. In all your other sheets, cell A1 will have a “Back to Index” link for easy navigation. If you want to use another cell for this backwards navigation, change the code in both places where it says A1 to whatever cell you’d like.
Need help? Use our nifty guide to help figure out how to install and use your macros.
Private Sub Worksheet_Activate()
'
'MACROS BY EXCELZOOM.COM
'
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("A1"), 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
