To Create a table of content in excel with macro we have to use a Hyperlink.Add Function In VBA which is similar to the Excel’s Hyperlink Function.
It is the part of Hyperlink collection method. Let’s have a look at Arguments of Add.Hyperlinks :
hyperlinks.Add(Anchor, Address, [SubAddress], [ScreenTip], [TextToDisplay])
Anchor: It can be a range or an object (Shape, Smart Art, Picture etc.). The anchor is like a storage place for the hyperlink.(Required)
Address & SubAddress As highlighted from the name, both contain the address of the hyperlink. The Address contains the link (URL, Path to File etc.). to a particular file, web address or server path, whereas SubAddress contains the link to a particular place in that file or web page. (Required)
Screen Tip It will highlight on the screen when mouse pointer paused on the hyperlink. And, if you don’t mention any ScreenTip in your code, then it will simply show the address of the hyperlink on screen. (Optional)
TextToDisplay This argument works to add a text in the anchor as a display. As it is an optional argument you can skip this. Without a TextToDisplay argument, the link address of the hyperlink will show at anchor. (Optional)
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