Create A Table Of Contents By VBA Code

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