Swipe to show menu

The monthly report pattern Every automated report in this course — follows the same shape: clear old state, filter or summarize the data, refresh or rebuild the visual output, apply presentation formatting, and confirm completion to the user.

Worked Example: One-Click Monthly Report

Option Explicit
 
Sub GenerateMonthlyReport()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim targetMonth As String
 
    Set ws = ThisWorkbook.Worksheets("Reports")
    Set tbl = ws.ListObjects("tblReports")
    targetMonth = "March"
 
    ' 1. Start from a clean slate
    If tbl.AutoFilter.FilterMode Then tbl.AutoFilter.ShowAllData
 
    ' 2. Filter to the month being reported on
    tbl.Range.AutoFilter Field:=1, Criteria1:=targetMonth
 
    ' 3. Refresh the summary PivotTable so it reflects current data
    On Error Resume Next
    ThisWorkbook.Worksheets("Pivot").PivotTables("ptProfitByRegion").RefreshTable
    On Error GoTo 0
 
    ' 4. Apply export-ready formatting
    With ws.PageSetup
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintArea = tbl.Range.Address
    End With
 
    ' 5. Confirm completion
    MsgBox targetMonth & " report is ready — filtered, " & _
        "refreshed, and print-formatted."
End Sub

The five numbered comments aren't just labels — they're the report pattern from earlier in this section made literal, one step per stage. A few details worth calling out specifically:

targetMonth As String, set to a fixed value near the top, is the one line Task asks you to change to "January" — because every later step reads from this single variable rather than the word "March" being repeated anywhere else in the Sub, changing the report's target month never requires touching more than one line;
Step 3 wraps RefreshTable in On Error Resume Next / On Error GoTo 0 for the same reason the Pivot-building macro did in section 4.3: if the Pivot sheet doesn't exist yet, this line would otherwise throw an error and stop the whole report macro cold, rather than just skipping a step that isn't ready yet;
Step 4's PrintArea = tbl.Range.Address ties the printable area directly to the Table's own address, so if rows are later added via ListRows.Add, the print area still exactly matches the data — no separate print-area maintenance required;
Step 5's MsgBox concatenates targetMonth into the confirmation text, so the message itself always names whichever month was actually just processed.

Dashboard Refresh

If a workbook contains several PivotTables and charts feeding a dashboard sheet, RefreshAll recalculates every data connection and PivotCache in one call — the one-line version of what GenerateMonthlyReport does by hand for a single PivotTable: ThisWorkbook.RefreshAll

This one line is doing the same job as step 3 in GenerateMonthlyReport, just at the scale of the entire workbook rather than one named PivotTable — useful once a dashboard has grown to include several PivotTables, external data connections, or linked queries that all need to stay in sync.

Export-ready Formatting

Beyond page setup, a finished report often needs to leave Excel entirely. ExportAsFixedFormat produces a PDF directly from code:

ws.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=ThisWorkbook.Path & "\March_Report.pdf", _
    Quality:=xlQualityStandard

ThisWorkbook.Path returns the folder the current workbook is saved in, without a trailing backslash — which is why the filename is built by concatenating "\March_Report.pdf" onto it explicitly. If ThisWorkbook hasn't been saved yet, .Path returns an empty string and this line would try to save to just "\March_Report.pdf" at the root of the current drive, so it's worth confirming the workbook has been saved at least once before relying on this pattern.

Task

Type GenerateMonthlyReport exactly as shown (you'll need the Pivot sheet from previous chapters to exist first) and run it. Confirm the table filters to March and the Pivot refreshes.
Change targetMonth to "January" and re-run — confirm the report updates to reflect the new month.
Add one line at the end of the Sub, before the MsgBox, that exports the Reports sheet to PDF using ExportAsFixedFormat as shown above.

Hints

1. Running GenerateMonthlyReport as-is

Make sure the Pivot sheet and ptProfitByRegion PivotTable from the earlier chapter task actually exist first — this Sub refreshes an existing PivotTable, it doesn't build one from scratch.
Type the procedure exactly as shown, run it, and check two things: the Reports table should now be filtered to show only March rows, and the Pivot sheet's numbers should reflect that (though the Pivot itself summarizes all months regardless of the Reports filter, since PivotCaches read the full range, not the filtered view).

2. Changing targetMonth to January

Only one line needs to change — the targetMonth = "March" assignment near the top.
Re-run the whole Sub and confirm the Reports table now filters to January instead.

3. Adding a PDF export line

This is the exact same ExportAsFixedFormat line from earlier in the chapter — you're exporting the ws worksheet, not the whole workbook.
Build the filename the same way the chapter's invoice-generation example did: combine ThisWorkbook.Path with a name that includes targetMonth, so each run produces a distinctly named file rather than overwriting the same one every time.
Placement matters: it needs to go after the filtering/refreshing/formatting steps are done, but before the final MsgBox confirms completion — otherwise the confirmation message would appear before the file actually exists.

Solution

Option Explicit

Sub GenerateMonthlyReport()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim chtObj As ChartObject
    Dim printRange As Range
    Dim targetMonth As String

    Set ws = ThisWorkbook.Worksheets("Reports")
    Set tbl = ws.ListObjects("tblReports")
    targetMonth = "January"

    ' 1. Start from a clean slate
    If tbl.AutoFilter.FilterMode Then tbl.AutoFilter.ShowAllData

    ' 2. Filter to the month being reported on
    tbl.Range.AutoFilter Field:=1, Criteria1:=targetMonth

    ' 3. Refresh the summary PivotTable so it reflects current data
    On Error Resume Next
    ThisWorkbook.Worksheets("Pivot").PivotTables("ptProfitByRegion").RefreshTable
    On Error GoTo 0

    ' 4. Build a print area that covers the table AND the chart
    On Error Resume Next
    Set chtObj = ws.ChartObjects(1)
    If Not chtObj Is Nothing Then
        Set printRange = Union(tbl.Range, ws.Range(chtObj.TopLeftCell.Address, _
            chtObj.BottomRightCell.Address))
    Else
        Set printRange = tbl.Range
    End If
    On Error GoTo 0

    On Error Resume Next
    With ws.PageSetup
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintArea = printRange.Address
    End With
    On Error GoTo 0

    ' 5. Export the filtered report to PDF
    ws.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=ThisWorkbook.Path & "\" & targetMonth & "_Report.pdf", _
        Quality:=xlQualityStandard

    ' 6. Confirm completion
    MsgBox targetMonth & " report is ready — filtered, refreshed, and exported."
End Sub

Run it once with targetMonth = "March" and once with "January" — you should end up with two separate PDFs (March_Report.pdf and January_Report.pdf) sitting next to your workbook, each reflecting the correctly filtered data at the time it ran.

Note

If a dialog pops up asking you to choose a printer (rather than the code failing outright), select any available option — including "Microsoft Print to PDF," "Microsoft XPS Document Writer," or any other printer listed, even a fax driver. The specific printer chosen doesn't matter here; VBA just needs some printer selected to satisfy the PageSetup/export process, since Excel routes these operations through the printer subsystem behind the scenes regardless of which one is active.

Everything was clear?

Thanks for your feedback!

Section 4. Chapter 5

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Building Automated Reports