The title above I think describing everything, even though I would like to explain the title briefly. Like you have a Workbook in excel containing 20+ or more sheets and its very annoying to navigate from one sheet to another by scrolling the scroll bar. There are many ways to do the navigation. By right-clicking on the lower-left corner (shown in the picture in red circle) of the sheets navigation panel, you can activate the navigation box and you can scroll to your desire sheet to navigate.
But today my topic is to do it in VBA and to create a list of all sheets in one index sheet and all available sheets will have the link to return in the index sheet. We can do it manually by inserting hyperlinks for each sheet in the index sheet and the target sheet to go back to the index sheet. But it will be quite time-consuming and frustrating. In VBA we can do it by one click. So, how we will do that in VBA? Let's have look at the code below. Codes credit goes to Sumit Bansal from trumpexcel.com. Though you can find many other website having similar codes.
Sub CreateSummary()
'Created by Sumit Bansal of trumpexcel.com
'This code can be used to create summary worksheet with hyperlinks
Dim x As Worksheet
Dim Counter As Integer
Counter = 0
Range("A4").Select
For Each x In Worksheets
Counter = Counter + 1
If Counter = 1 Then GoTo Donothing
With ActiveCell
.Value = x.Name
.Hyperlinks.Add ActiveCell, "", x.Name & "!A1", TextToDisplay:=x.Name, ScreenTip:="Click here to go to the Worksheet"
With Worksheets(Counter)
.Range("A1").Value = "Back to " & ActiveSheet.Name
.Hyperlinks.Add Sheets(x.Name).Range("A1"), "", _
"'" & ActiveSheet.Name & "'" & "!" & ActiveCell.Address, _
ScreenTip:="Return to " & ActiveSheet.Name
End With
End With
ActiveCell.Offset(1, 0).Select
Donothing:
Next x
End Sub
The code above will create a nice list of all your sheets in the active sheet. Also, it will create a backward link for the index sheet in all the sheets.
How to Apply the code
Open VBA code editor by pressing Alt+F11 from your excel workbook or Go to the Developer tab and select Visual Basic for Application. In the VBA editor, from the menu ribbon click Insert and choose Module.
Double click to the module, the code window will open. Paste all the code mentioned above.
Save and run the code. Work is done!!
If you want to access the code from your sheet, then insert a button and link the macro with it.
Tricky Part
Cell A1 has been hardcoded in this code. Please change it according to your needs. Before starting the loop I had added one line code extra which was not included in the original code.
Range("A4").select
This line will generate your navigation list from Cell A4. With Sumit Bansal code the list is generating from the active cell.
