Step 1: Press the Shortcut keys Alt + F11 to open the Visual Basic for Application window.
Step 2: In the Visual Basic for Applications window, Click Insert > Module, then Copy the below VBA code into the code window.
Sub CreateTOC() 'Created by Yoda Learning Academy 'This VBA code will create/refresh a table of contents. 'Just click it... and get it done!! ' ' Dim lngIdxRow As Long Dim lngSrNo As Long Dim objWS As Worksheet Dim objTOCSht As Worksheet 'In following code, change name of the index sheet within the double quotes Set objTOCSht = ThisWorkbook.Sheets("Auto_TOC") lngIdxRow = 6 lngSrNo = 1 'Follwing code will create a Table of Contents With objTOCSht 'Clear/Initialize the existing TOC .Range("A" & lngIdxRow & ":B" & lngIdxRow + 255).Clear .Range("A" & lngIdxRow).Value = "Sr#" .Range("B" & lngIdxRow).Value = "Worksheet Name" lngIdxRow = lngIdxRow + 1 For Each objWS In ThisWorkbook.Sheets If objWS.Name <> .Name Then .Cells(lngIdxRow, 1).Value = lngSrNo .Hyperlinks.Add Cells(lngIdxRow, 2), "", _ "'" & objWS.Name & "'!A1", , objWS.Name lngSrNo = lngSrNo + 1 lngIdxRow = lngIdxRow + 1 End If Next objWS 'Following code will format the TOC table With .Range("A6:B" & lngIdxRow - 1) .Font.Size = 12 .Font.Bold = True .IndentLevel = 1 .RowHeight = 18 .VerticalAlignment = xlCenter .Borders(xlEdgeLeft).LineStyle = xlContinuous .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlInsideHorizontal).LineStyle = xlContinuous .Borders(xlInsideVertical).LineStyle = xlContinuous End With .Range("A6:A" & lngIdxRow - 1).HorizontalAlignment = xlCenter .Range("A1").Select End With End Sub
Step 3: Create a Button From Developer Tab > Insert > Under Form Controls Than Assign the Marcos of Table of Contents
Excel VBA Macros for Non-Coders

Do you find VBA Macros scary? Did you miss any job opportunity because of it? Get started today with our eBook guide on using – Excel VBA Macros. 140 pages of rich visuals. Download now.