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 lets you quickly and easily create an index in Excel that lists all sheets in your workbook. The best part is that the index excel macro updates itself every time you select the index sheet! Indexing in Excel made simple.
If you need an index sheet in your file, you probably already have a zillion worksheets in your file, here is how to make an index in Excel
Automatically Create Index in Excel
- Add a tab and call it “Index” or whatever you want to identify it as an index (table of contents, etc.).
- Right click the Index tab and select ‘View Code’.
- Enter the VBA code below. Click on another sheet in your file, then click back on your Index sheet. Hey presto! 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. Here is what your index in Excel will look like when done.
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 - create index in excel
'
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
We hope this how to index in excel guide helps making an index in excel painless and easy for you. We love this macro code and have opted to make it available to all free of any charge and unrestricted from our members area so you can enjoy the wizardry of this auto updating index in excel :-) Find that tab in excel with your new Index Sheet and impress all your colleagues in the process..
Have some comments about indexing in Excel, we would love to hear them