Sheet Index macros

If you have ever worked with Excel workbooks containing lots and lots of sheets, you will know how tricky and time consuming it can be to find your way around to the sheets you need to work with. The following are two macros to add to your Quick Access Toolbar, that can 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

>>Watch now my tutorial on You Tube how to add the macro buttons to your QAT

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 from Amazon for step by step directions:

Buy it now on Amazon.co.uk. For a short time only £1 GBP

And only $1.35 USD on Amazon.com.