Excel VBA CodeTutorials

Create A Table Of Contents By VBA Code

1 Mins read

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.

Related posts
AlteryxTutorials

Alteryx Hotkeys - Alteryx Keyboard Shortcuts

2 Mins read
Top 50+ Alteryx Shortcuts for Windows. Alteryx is popularly known as a Self-Service Analytics tool. Business users can build their data workflows…
AlteryxTutorials

Step By Step Guide to Learn Alteryx

6 Mins read
Alteryx Learning Path: The growth in technology has resulted in growth in understanding. In today’s world, humans – fellow businessmen know the…
Excel VBA CodeTutorials

VBA Code to Clean the Date Format

1 Mins read
When it is useful? Most of the time the most annoying problem is when the data is taken from ERP or other…