Do you want one central worksheet where you can easily navigate to any worksheet in your workbook, and then navigate back with one click? This Excel macro creates an index 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.

If you need an index sheet in your workbook, you probably already have loads of worksheets in your workbook, but the first step is to add one more worksheet, and call it “Index”, or whatever you want to identify it as an index (table of contents, etc.). To rename the tab, right click on the tab and select rename.

You will need to ensure that your developer ribbon is turned on. To turn on the developer ribbon, go to File, then options, Customize ribbon and then in the Mains tab, ensure that Developer is checked.

Next, right click the Index tab and select ‘View Code’. This is where you will enter the code below.

Private Sub Worksheet_Activate()
‘
‘The Excel Club com macro example
‘
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

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.

By adding the code with the method above, we have added it directly to the Excel worksheet in the active workbook. There are other methods of adding Macros and over the next few weeks we will look at more code and more ways to add the code. Understanding code can be difficult for most people but by the end of this series, you will have a far better understanding of VBA and Excel Macros.

***Download our NEW app and Learn Excel Fast.