Building a Report Automation Macro

Analogy categoryAnalogies

The Report Automation Problem

A classic VBA use case is the recurring report: every month someone pastes raw transaction data into a workbook, then manually pivots it, formats a summary sheet, and emails a PDF. Automating this eliminates hours of repetitive clicking and, more importantly, removes the human errors that creep in when the same steps are done by hand. A well-built report macro follows a clear pipeline — gather the source data, transform it into a summary, format the output, then export or distribute — and each stage should be a separate, testable Sub so the whole thing is maintainable rather than one thousand-line monolith.

🏏

Cricket analogy: A report macro is like a well-drilled fielding routine: gather, relay, and finish the run-out in practised stages, so the same play executes flawlessly every match instead of improvising each time.

Stage 1: Gather and Stage the Data

The first stage locates the raw data and stages it into a known place. Rather than assuming the data is on the active sheet, reference it explicitly — ThisWorkbook.Worksheets("Raw") — and dynamically find the last used row so the macro adapts to any number of records. A robust macro validates its inputs here: confirm the expected columns exist and the sheet is not empty before proceeding. This defensive step prevents the macro from silently producing a blank or wrong report when someone forgets to paste the data, which is the most common real-world failure of report automations.

🏏

Cricket analogy: Staging the data is like the groundsman preparing the pitch and checking the boundary ropes before play — if the setup is wrong, no amount of good batting later fixes it.

vba

Option Explicit

Sub BuildMonthlyReport()
    On Error GoTo CleanFail
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim wsRaw As Worksheet, wsRep As Worksheet
    Set wsRaw = ThisWorkbook.Worksheets("Raw")

    Dim lastRow As Long
    lastRow = wsRaw.Cells(wsRaw.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then Err.Raise vbObjectError + 1, , "No data found on 'Raw' sheet."

    ' Recreate a clean report sheet
    On Error Resume Next
    ThisWorkbook.Worksheets("Report").Delete
    On Error GoTo CleanFail
    Set wsRep = ThisWorkbook.Worksheets.Add(After:=wsRaw)
    wsRep.Name = "Report"

    ' Stage 2: summarise Amount (col C) by Region (col B) using a Dictionary
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    Dim data As Variant
    data = wsRaw.Range("A2:C" & lastRow).Value
    Dim i As Long, region As String, amt As Double
    For i = 1 To UBound(data, 1)
        region = CStr(data(i, 2))
        amt = CDbl(data(i, 3))
        dict(region) = dict(region) + amt
    Next i

    ' Stage 3: write and format the summary
    wsRep.Range("A1:B1").Value = Array("Region", "Total Amount")
    wsRep.Range("A1:B1").Font.Bold = True
    Dim r As Long: r = 2
    Dim k As Variant
    For Each k In dict.Keys
        wsRep.Cells(r, 1).Value = k
        wsRep.Cells(r, 2).Value = dict(k)
        r = r + 1
    Next k
    wsRep.Range("B2:B" & r - 1).NumberFormat = "#,##0.00"
    wsRep.Columns("A:B").AutoFit

    ' Stage 4: export to PDF next to the workbook
    Dim pdfPath As String
    pdfPath = ThisWorkbook.Path & Application.PathSeparator & _
              "Report_" & Format(Date, "yyyy_mm") & ".pdf"
    wsRep.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath

CleanExit:
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    MsgBox "Report built and exported to:" & vbCrLf & pdfPath, vbInformation
    Exit Sub
CleanFail:
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    MsgBox "Report failed: " & Err.Description, vbExclamation
End Sub

Stage 2: Summarize with a Dictionary

The heart of most reports is aggregation — totalling amounts by category, region, or date. A Scripting.Dictionary is the idiomatic VBA tool for this: it maps each unique key (say, a region) to an accumulating value, letting you build the summary in a single pass over the data with no pre-sorting. The pattern dict(region) = dict(region) + amt works because reading a missing key returns an empty value that coerces to zero, so the first occurrence initializes the total naturally. This is far faster and cleaner than nested loops or repeated worksheet formulas, and it scales to tens of thousands of rows without noticeable slowdown.

🏏

Cricket analogy: The Dictionary is like a scorer tallying runs per batsman on one sheet — each name accumulates its total in a single pass through the over-by-over log.

ExportAsFixedFormat with Type:=xlTypePDF is the built-in way to produce a PDF — no external library needed. Point it at a worksheet or range object and it renders exactly what would print, honouring your print area and page setup.

Formatting, Exporting, and Scheduling

Once the summary exists, formatting turns raw numbers into a professional deliverable: bold headers, a NumberFormat like "#,##0.00" on currency columns, AutoFit on columns, and perhaps a total row. The final stage exports — ExportAsFixedFormat produces the PDF, and you can extend this with Outlook automation (CreateObject("Outlook.Application")) to email the file automatically. For truly hands-off operation, the Workbook_Open event or Application.OnTime can schedule the macro, though many teams prefer to trigger it with a button so a human confirms the data is ready. Keeping each stage in its own Sub means you can test the summary logic without exporting, or re-run formatting without recomputing.

🏏

Cricket analogy: Formatting the report is the presentation ceremony after the match — the same result, but dressed up with the trophy, medals, and a tidy scoreboard for the audience.

Deleting a sheet with Worksheets("Report").Delete triggers a confirmation prompt and errors if the sheet does not exist. Wrap the delete in a narrow On Error Resume Next, and set Application.DisplayAlerts = False around it, remembering to restore DisplayAlerts afterward.

Structure a report macro as a pipeline: gather, summarize, format, export — each stage its own Sub.
Reference source sheets explicitly and find the last row dynamically instead of assuming the active sheet.
Validate inputs early (sheet not empty, columns present) to avoid silently wrong reports.
Use a Scripting.Dictionary to aggregate by key in a single pass over a Variant array of the data.
Format output with bold headers, NumberFormat, and AutoFit to produce a professional deliverable.
Use ExportAsFixedFormat (xlTypePDF) for PDF output with no external library.
Extend with Outlook automation for email, and schedule via Workbook_Open, OnTime, or a button trigger.