Automate Creation of Table of Content Sheet for Large Excel Files with Multiple Sheets

For sure we all at some point work with excel in our day to day life irrespective of professions. Often these files have a large number of sheets. However, it is easy to navigate sheets when no of sheets are not much, but if they exceed a certain limit, hands down it is a very painful task to navigate them.

Now one solution is to use a special window using f5 or ctrl+g. But there is a catch, you need to mention the name of the sheet with a lot of stop words and a cell number.

Another useful way is to create a Table of the Content sheet at the beginning to link all the sheets and a reverse link to the TOC sheet from those sheets.

Now, it is easy to create a Table of the Content sheet, but it is not very enjoyable when you need to make changes to the file every time you alter anything in sheet name or add a new sheet or remove some.

A few days back I was going through a course in Udemy conducted by Leila Gharani, and I found a very useful way to create TOC sheet using VBA. However, I have modified the code a little bit to allow non-VBA users to take the full out of the code.

The approach is to create a tool that will link every sheet with the Table of the Content sheet in using a hyperlink, and the TOC sheet will also be linked to each sheet. We will be able to navigate each sheet from the TOC sheet and TOC sheet from each sheet.

One can add this macro to the personal workbook and create a button in the quick access toolbar or in the ribbon to access it anytime. Feel free to modify this as per requirement. Hope this will help a few.

Sub add_toc()

Dim strt_cel As Range

Dim sh As Worksheet

Dim sh_name As String


If Sheets(1).Name = "TOC" Then GoTo lv

Sheets.Add(Before:=Sheets(1)).Name = "TOC"

Sheets("TOC").Activate


On Error Resume Next

Range("B2:C2").Value = Array("Sheet Name", "Sheet Content")

Range("B2:C2").HorizontalAlignment = xlLeft

Range("B2:C2").Font.Color = vbBlue

Range("B2:C2").Interior.Color = vbYellow


Set strt_cel = Range("B3")


For Each sh In Worksheets

    sh_name = sh.Name

    If ActiveSheet.Name <> sh_name And sh.Visible = xlSheetVisible Then

    ActiveSheet.Hyperlinks.Add Anchor:=strt_cel, Address:="", SubAddress:= _

        "'" & sh_name & "'!A1", TextToDisplay:=sh_name

    strt_cel.Offset(0, 1).Value = sh.Range("B1")

    Set strt_cel = strt_cel.Offset(1, 0)

    End If

Next sh

Columns("B:C").EntireColumn.AutoFit

 
'in the below code, Range ("B1") indicates the cell which we are linking with the TOC sheet for reverse link. 

For Each sh In Worksheets

    If sh.Name <> "TOC" Then

    sh.Hyperlinks.Add Anchor:=sh.Range("B1"), Address:="", SubAddress:= _

        "TOC!A1", ScreenTip:="Goto TOC", TextToDisplay:=sh.Range("B1").Value

    End If

    sh.Range("B1").Font.Color = vbBlue

Next sh

Exit Sub


lv:

    MsgBox "There is already a Table of Content sheet. To create new sheet delete the previous TOC sheet"

 

End Sub