When you're working with sheets of data on Excel, it is easy to get lost among the sheets. However, with this little VBA code, you can create a Table of Contents Page with link to each of the sheets. Then you can easily navigate between sheets back and forth.
Here is the code below. We are going to break down to understand how it works.
Sub TableofContent
Dim i As Long
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Table of Content").Delete
Application.DisplayAlerts = True
On Error GoTo 0
ThisWorkbook.Sheets.Add before:=ThisWorkbook.Worksheets(1)
ActiveSheet.Name = "Table of Content"
For i = 1 To Sheets.Count
With ActiveSheet
.Hyperlinks.Add _
Anchor:=ActiveSheet.Cells(i, 1), _
Address:="", _
SubAddress:="'" & Sheets(i).Name & "'!A1", _
ScreenTip:=Sheets(i).Name, _
TextToDisplay:=Sheets(i).Name
End With
Next i
End Sub
First, we named the subprocedure as TableofContent and defined a variable called i to use it in the for loop.
Then if exists, we are deleting the previous Table Of Content page to create a new one.
Next, we are stating that the new Table of Content sheet, will be placed before the first sheet.
Then the real For loop magic happens. We are looping through each sheet index and create a hyperlink for them in the Table of Content sheet.
Now with this code, you can easily create a Table of Content and navigate through sheets.
However, after you click and activate any sheet, you should be able to navigate back to the Table of Content sheet to easily go back and forth. To do that, you can create a macro with just two lines below and assign it to a shape.
Sub GotoMainPage()
Sheets("Table of Content").Select
Sheets("Table of Content").Range("A1").Select
End Sub
After pasting this code to a module, you can assign this macro to a shape of your choice, and your back to main page is on your service.
Also please keep in mind that, once you have applied these macros, Excel will ask you to save your file as "XLSM" ( macro enabled). There will be no change in your file and data in it. It will be just macro enabled version of your file.
If you liked this project, please don’t forget to share and comment below.
