By using vba.booksticle.com you agree to our cookie policy, We and our partners operate globally and use cookies, for multiple purposes

948 Steps to 6 Figures by Learning Excel-VBA and Other Skills



Step 270 - Add a Table of Contents


In this project, we will be adding a Table of Contents sheet to any spreadsheet.

Steps
  • Download the Add table of contents.017.xlsm spreadsheet
  • Enable Editing
  • Enable Content
  • Press [Run] or type in file to yellow cell G1 and press [Run]
  • The file will be opened
  • The TOC (Table of Contents) is added
  • File will be saved with "-toc" appended to file name and the original file unchanged
  • File will be saved with "xlsm" suffix
  • A statistical message will be posted to Cherper.com to the public feed


    Algorithm
  • select spreadsheet to add TOC code to
  • Add TOC page
  • Run Process
  • Save with extension-toc
  • no macros added

    Process
    First phase is to use macros I will supply to you.

    Second phase is to build the code from scratch.

    Problem

    As a spreadsheet gets large with many tabs or sheets it becomes time-consuming to go to the sheet you want to update.

    As an example I have 167 sheets in the workbook.

    Complexity
  • not for beginners


    Solutions
  • Use Excel to find the information
  • Get a solution from the internet
  • Develop your own solution

    Similar solutions
  • Logger code

    Requirements
  • take any workbook and add a TOC sheet with hyperlinks to each sheet. A deletion function and an Archive function
  • Prompt user for spreadsheet name
  • Open spreadsheet a copy
  • add TOC sheet
  • Instruct user to save with a new name

    Notes
  • allow user to make mistakes
  • Use debugger to understand what the code is doing
  • Teach user how to create this code
  • user to type in the code from an image
  • teach user to use google to build this

    Algorithm first try
  • Go through each sheet in the workbook
  • Add it to the table of contents sheet
  • Add a hyperlink to the sheet
  • Handle case where the sheet already exists in the TOC
  • Handle can where sheet was removed
  • What to do with hidden sheets
  • Count the number of rows * columns in use and shapes



    Requires
  • Large spreadsheet with many tabs
  • Could use the code to build it from a list

    Prerequisites
  • Excel
  • Taken the introduction and passed assessment

    Short for Table of Contents sheet.

    This is a sheet that helps you with large excel workbooks (i.e. many sheets)

    It's a set of VBA code that can be used as an overview and the other sheets are details.

    At work, I had a report in Excel where each tab was complex data about a company and the first sheet was an overview with list of the companies, some overview data and a hyperlink to the sheet the data was on.

    The process does the following for a new sheet:
  • Create the toc sheet if it doesn't exist
  • Go through each sheet and get the sheet name
  • Add the sheet name with a hyperlink to the Toc sheet

    I also created a version that would just update the Toc sheet by removing sheets that no longer existed and adding new sheets.

    Concepts to know

    1. Accessing a collection
    2. Activesheet object
    3. Application object
    4. Arrays
    5. assign a macro to a button "OnAction"
    6. [brackets]
    7. Buttons
    8. Calling a subroutine
    9. Cells(r,c).select
    10. ClearFormats method range object
    11. Collections
    12. Comments
    13. Delete method on rows, selection
    14. Dim
    15. DispYN
    16. Displayalerts
    17. EnableEvents
    18. Err
    19. error handling xl and vba
    20. Evaluate method
    21. Exit For
    22. Exit Sub
    23. FindLastrow
    24. File Dialog
    25. File Suffixes
    26. for each loop on a collection
    27. for loops negative increments
    28. for i = r to 2 step -1
    29. Goto
    30. Hyperlinks
    31. If - Else
    32. Initialize a variable
    33. IsError()
    34. Labels: for goto
    35. Left()
    36. Len( )
    37. match
    38. Mid
    39. Msgbox
    40. Multi-select
    41. Nested (())
    42. Null string
    43. Object.Property
    44. On error
    45. Open an excel file aka workbook
    46. Parameters
    47. properties
    48. Range
    49. Range names
    50. Rows collection
    51. Run a macro with run statement
    52. Run a macro on workbook that's not thisworkbook
    53. SaveAs
    54. Shapes collection
    55. Sheets
    56. Sheet.name
    57. Selecting a sheet
    58. string concatenation
    59. "" vs """"
    60. selecting a cell
    61. Select a sheet
    62. Selection object
    63. Selection
    64. Sheets collection
    65. Subroutines
    66. Thisworkbook
    67. Value property
    68. Workbook object
    69. [Workbook]Sheet!A:A
    70. Workbooks collection
    71. Recording code
    72. Variant variables
    73. vlookup
    74. With - End With
    75. ""


    Process load the code and step through it



    Sub AddTOCSheet()
    Dim sh, r As Long
    Dim i As Long
    Dim sName As String

    AllSheets.Select
    For Each sh In Sheets
        If IsError(Application.Evaluate("=match(""" & sh.Name & """,A:A,False)")) Then
            r = FindLastRow(1) + 1
            Cells(r, 1).Select
            Selection = sh.Name
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & sh.Name & "'!A1", TextToDisplay:=sh.Name
        End If
    Next

    r = FindLastRow(1)
    For i = r To 4 Step -1
     On Error Resume Next
     sName = Sheets(Cells(i, 1).Value).Name
     If Err <> 0 Then
        Rows(i).EntireRow.Delete
     End If
    Next
    End Sub

    •    Table of Contents | Send us your feedback | © 2025 All Rights Reserved | Edit