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
Great macros… is there a way to set a fixed link in the index? So that the marcos skips a specific line and continues with the automated index in the next line.
When I run this macro I get a “compile error: Invalid use of Me keyword” – any way I can fix this?
Hi Scott. Did you insert this code in the worksheet’s code as described in the second paragraph? Also try copying and pasting the code directly from above, as opposed to typing it in. If all else fails, you can send me a copy of the file you’re trying to use (or save another copy and remove confidential data, etc) by using this contact form: http://excelzoom.com/contact/@Scott
I was hoping to store this in my personal macro workbook but it wont run as a macro. Can you explain why? It would be very helpful across a number of workbooks but if I have to install on each one then it isnt as useful for me.
Can’t thank you enough for this.
Sweet! Thanks!
is it possible to have the code without the return to index hyperlink added? I need the original column header displayed
Thanks
@LEON
Just remove the following code:
With wSheet
.Range(“A1″).Name = “Start_” & wSheet.Index
.Hyperlinks.Add Anchor:=.Range(“A1″), Address:=”", _
SubAddress:=”Index”, TextToDisplay:=”Back to Index”
End With