Creating a summary sheet in Excel using VBA, with formulas that link to other sheets, involves automating the process of consolidating data. Here’s a step-by-step guide to help you achieve this:

Step 1: Set Up Your Workbook

• Assume you have multiple sheets with similar structures from which you want to aggregate data into a summary sheet.
• Each sheet could represent different categories like months, departments, etc.

Step 2: Plan Your Summary Sheet

• Decide what kind of summaries you need (e.g., sum, average, count).
• Determine the layout of your summary sheet.

Step 3: Enable the Developer Tab

• If not already visible, enable the Developer tab in Excel to access the VBA editor:

1. Go to Excel Options.

2. Click on “Customize Ribbon.”

3. Check “Developer” in the right-hand panel.

Step 4: Write a VBA Script

• Open the Visual Basic for Applications editor (`ALT` + `F11`).
• Insert a new module by right-clicking on your workbook in the Project Explorer, selecting `Insert`, and then `Module`.

Here is a basic template for a VBA script to create a summary sheet linking to data from other sheets:

Sub CreateSummarySheet()
    Dim wsSummary As Worksheet
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim summaryRow As Long
    Dim colNum As Integer
    Dim sheetCount As Integer

    ' Add or clear existing Summary sheet
    On Error Resume Next
    Set wsSummary = ThisWorkbook.Sheets("Summary")
    If Not wsSummary Is Nothing Then
        wsSummary.Cells.Clear
    Else
        Set wsSummary = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        wsSummary.Name = "Summary"
    End If
    On Error GoTo 0

    ' Initial setup for Summary Sheet
    wsSummary.Cells(1, 1).Value = "Sheet Name"
    wsSummary.Cells(1, 2).Value = "Total" ' Update with your requirement like total, average

    summaryRow = 2
    sheetCount = ThisWorkbook.Sheets.Count

    ' Loop through each sheet and create formulas
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> wsSummary.Name Then
            lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
            ' Assuming the values are in column B, change as necessary.
            colNum = 2 

            ' Write sheet name and sum formula
            wsSummary.Cells(summaryRow, 1).Value = ws.Name
            ' Insert formula linking to each sheet
            wsSummary.Cells(summaryRow, 2).Formula = "=SUM('" & ws.Name & "'!" & ws.Cells(2, colNum).Address & ":" & ws.Cells(lastRow, colNum).Address & ")"

            summaryRow = summaryRow + 1
        End If
    Next ws
    
    ' Optional: Autofit columns
    wsSummary.Columns("A:B").AutoFit
End Sub

Explanation of Code:

• Initialize: Create a new worksheet named “Summary” or clear existing if it already exists.
• Headers: Set the headers on the summary sheet.
• Loop: Iterate through each sheet, skipping the summary sheet.
• Formulas: Construct and insert a formula for each worksheet to sum a specific column. Adjust the column and formula as necessary to fit your needs.
• Output: The summary sheet will display the sheet name and the corresponding formula result.

Step 5: Run the VBA Script

• Press `F5` in the VBA Editor or run the macro from Excel by pressing `ALT` + `F8`, select `CreateSummarySheet`, and click `Run`.

Step 6: Adjust & Customize

• Modify the script to include other formulas or additional columns based on what you need in your summary (like averages, min, max, etc.).
• Customize how the data is displayed and what additional information is added.

This process will automatically populate the summary sheet with updated links to each sheet’s data every time you run the macro. Adjust the column numbers and sheet structures as needed for your specific use case.

Follow us on Social Media

facebooklinkedin

Unlock Your Potential

Excel

Access

Power BI

Help us grow the project