Sheet Index macros

If you have ever worked with Excel workbooks containing lots and lots of sheets, you’ll know how tricky and time consuming it can be to find your way around the worbook. The following are two macros to add to your Quick Access Toolbar, to help you easily navigate and work with very large Excel workbooks.

• • The Sheet Index macro instantly generates a new worksheet, listing all the worksheets as hyperlinks.  So you can jump instantly to any worksheet you want, by just clicking on the link.

• • The Goto Index macro complements the Sheet Index macro. It lets you return instantly to the Index sheet from any sheet and then back to the same sheet again.

Sheet Index

This macro button adds a new sheet to your Excel workbook with a list of hyperlinks to all the sheets in the workbook. Clicking on a link takes you instantly to the named sheet. The index sheet will name. If your sheets are all jumbled up, you can always sort the sheet index

Sub sheet_index()
Dim wsht As Worksheet
Dim count As Integer
On Error Resume Next
Worksheets.Add before:=Worksheets(1)
'Delete the next line of code if you want to rename the Index sheet manually
Worksheets(1).Name = "Index"
Range("a1").Select
For Each wsht In Worksheets
If wsht.Index <> 1 And Worksheets(wsht.Name).Visible = True Then
    With ActiveCell
        .Value = wsht.Name
        .Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="'" & wsht.Name & "'!A1", TextToDisplay:=wsht.Name
    End With
ActiveCell.Offset(1, 0).Select
End If
Next wsht
Worksheets(1).Select
End Sub

Goto Index

The Goto Index macro button returns you instantly to the Index tab from any sheet within the workbook. It also remembers the previous sheet you were on, so you can go back to it by clicking the button again. If there isn’t a sheet named Index, the macro will take you to the first sheet on the left of the sheet tabs.

Sub goto_index()
On Error Resume Next
If ActiveSheet.Index = 1 Or ActiveSheet.Name = "Index" Then
    ActiveWorkbook.Worksheets(cursheet).Activate
    End
End If
cursheet = ActiveSheet.Name
Worksheets("Index").Activate
If ActiveSheet.Name <> "Index" Then
    Worksheets(1).Activate
End If
End Sub

How to add the Macro buttons to your Quick Access Toolbar

To learn how to enable macros to run, and how to save the above VBA macro code to your Excel, please download my e-book on Amazon, Making the Most of the Quick Access Toolbar in Excel, for step by step directions: