How to create a summary sheet that lists all other sheets using VBA?

Creating a summary sheet that lists all other sheets in an Excel workbook using VBA can be very useful for quick navigation or overview purposes. Below is a step-by-step guide and example code to help you achieve this:

Step-by-Step Guide

Sub CreateSummarySheet()
    Dim ws As Worksheet
    Dim summarySheet As Worksheet
    Dim i As Integer
    
    ' Check if "Summary" sheet already exists
    On Error Resume Next
    Set summarySheet = ThisWorkbook.Sheets("Summary")
    On Error GoTo 0
    
    ' If "Summary" sheet exists, delete it
    If Not summarySheet Is Nothing Then
        Application.DisplayAlerts = False
        summarySheet.Delete
        Application.DisplayAlerts = True
    End If
    
    ' Add a new "Summary" sheet
    Set summarySheet = ThisWorkbook.Sheets.Add
    summarySheet.Name = "Summary"
    
    ' Set headers
    summarySheet.Cells(1, 1).Value = "Sheet Name"
    summarySheet.Cells(1, 2).Value = "Index"
    
    ' Loop through each worksheet in the workbook
    i = 2 ' Start from the second row
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Summary" Then
            summarySheet.Cells(i, 1).Value = ws.Name
            summarySheet.Cells(i, 2).Value = ws.Index
            i = i + 1
        End If
    Next ws
    
    ' Format headers
    summarySheet.Rows(1).Font.Bold = True
    summarySheet.Columns("A:B").AutoFit
End Sub

Open Excel and Access the VBA Editor:

Open your Excel workbook.
Press `ALT` + `F11` to open the VBA Editor.

Insert a New Module:

In the VBA Editor, go to `Insert` > `Module` to create a new module.

Write the VBA Code:

Copy and paste the following VBA code into the module window.

Run the VBA Code:

Close the VBA Editor and go back to Excel.
Press `ALT` + `F8` to open the Macro dialog box.
Select `CreateSummarySheet` and click `Run`.

Explanation of the Code:

Sheet Existence Check: The script first checks if a “Summary” sheet already exists, and if it does, it deletes it to avoid duplicates.
Create a New Summary Sheet: A new worksheet named “Summary” is added to the workbook.
Headers: Two headers are set, “Sheet Name” and “Index”, to list the sheet names and their respective index numbers.
Loop Through Worksheets: The script loops through each sheet in the workbook, excluding the “Summary” sheet, and writes each sheet’s name and index to the summary.
Formatting: The code sets the headers to bold and auto-fits the columns for better readability.

By running this macro, you will generate a summary sheet listing all other worksheets in your workbook, which can be especially useful for larger workbooks with numerous sheets.

facebooklinkedin