How to Automate Your Reporting with Excel Macros: A Complete Walkthrough

The textbook case for a macro is the report you build the same way every single month: drop in the export, format it, save a PDF, email it round. Once you know how to record and edit a macro and the habits that keep them tidy, the payoff is building one real pipeline — a macro that turns this month’s raw data into a finished, distributed report on a single click. This walks through that pipeline end to end: format, export, send, and tie it together.

The pipeline at a glance

A reporting macro is really several small macros run in sequence. Breaking it into stages keeps each piece simple and reusable.

1. Clean the raw export into a consistent shape
2. Format it into a presentable report
3. Export it as a dated PDF
4. Send or save it to where it needs to go

Each stage becomes its own focused macro, and a final “conductor” runs them in order. This structure is the whole secret to maintainable automation: when the formatting needs tweaking, you edit one small macro, not a sprawling monolith. Build the stages once, and the monthly report collapses from a half-hour of clicking into a single keystroke. Microsoft’s VBA getting-started guide is the reference for the language.

Format the report in code

The formatting stage is where a recorded macro shines — record yourself styling the report once, then clean up the code. A compact version:

Sub FormatReport()
    With ActiveSheet.UsedRange
        .Rows(1).Font.Bold = True
        .Columns.AutoFit
        .Borders.LineStyle = xlContinuous
    End With
End Sub

The With ActiveSheet.UsedRange block applies bold headers, auto-fit columns, and borders to the whole data region in a few lines. Because it targets UsedRange rather than a fixed range, it adapts to however many rows this month’s data has — a small detail that makes the macro work every month without editing. Start by recording the formatting you do by hand, then trim the recorder’s verbose .Select lines into this tighter form. You can extend this stage with anything you’d normally do to dress up a report — freeze the header row, add a title with the month, set a print area, color the totals row. Each is one or two more lines inside the With block, and because you only build it once, it’s worth getting the report looking exactly right rather than settling for “good enough” the way you might when formatting by hand under time pressure.

Export to PDF automatically

The export stage saves the formatted sheet as a PDF, with a filename built from the current month so every run is named correctly without you typing it.

Sub ExportPDF()
    Dim fn As String
    fn = ThisWorkbook.Path & "\Report_" & Format(Date, "yyyy-mm") & ".pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fn, OpenAfterPublish:=False
End Sub

ExportAsFixedFormat is VBA’s built-in PDF export, and the clever part is the filename: Format(Date, "yyyy-mm") stamps the current year and month, so May’s run saves “Report_2026-05.pdf” automatically. The file lands next to the workbook (ThisWorkbook.Path), and OpenAfterPublish:=False keeps it from popping open mid-run. One stage, and your report is a properly-named PDF every month with zero manual saving.

Send it, or just save it

The final stage delivers the report. The simplest version just leaves the PDF in a folder; the fuller version emails it through Outlook.

Sub EmailReport()
    Dim olApp As Object, mail As Object
    Set olApp = CreateObject("Outlook.Application")
    Set mail = olApp.CreateItem(0)
    mail.To = "[email protected]"
    mail.Subject = "Monthly Report " & Format(Date, "mmmm yyyy")
    mail.Attachments.Add ThisWorkbook.Path & "\Report_" & Format(Date, "yyyy-mm") & ".pdf"
    mail.Display   'use .Send to send without review
End Sub

This builds an Outlook email with the dated PDF attached and a subject line that names the month. Using .Display rather than .Send opens the email for a final glance before you send — a sensible default for anything going to other people. Emailing needs Outlook installed and macro automation permitted; if that’s not your setup, skip this stage and just have the macro save the PDF to a shared folder, which is often all a team needs anyway.

Tie it together into one click

Now the conductor: one macro that runs the stages in order, wired to a button on the sheet.

Sub RunMonthlyReport()
    Call FormatReport
    Call ExportPDF
    Call EmailReport
    MsgBox "Monthly report done."
End Sub

Each Call runs a stage you’ve already built and tested, and the final MsgBox confirms completion. Assign RunMonthlyReport to a button (or store it in your Personal Macro Workbook so it’s available everywhere), and the entire report — format, export, email — runs from a single click. That’s the moment the automation pays off: a task that was a careful half-hour becomes a button you press while the coffee brews. And because the stages are independent, the pipeline is easy to adapt — drop in a different export format, swap the email for a save-to-folder, add a stage that refreshes a pivot first. You’re rearranging tested pieces, not rewriting from scratch each time the report’s needs shift.

Run it on a schedule

The ultimate version of report automation runs without you pressing anything at all. There are two ways to get there, depending on how hands-off you need it.

Event-driven and scheduled runs are the step from “one click” to “no clicks,” but treat them with care — a macro that fires on its own is wonderful until it does something unexpected and nobody’s watching. Add a clear log or a confirmation email so you know each run happened and succeeded, and keep the scheduled version conservative (generate and save, rather than auto-send to clients) until you trust it completely. For most people the one-click conductor is the sweet spot; scheduling is there when the report is regular enough to truly automate away.

Make it reliable

Automation that runs unattended needs to be trustworthy. A few habits keep it from failing silently or doing damage.

• ✓ Comment every stage so its job is clear months later
• ✓ Save a copy before any destructive run — macro actions can’t be undone
• ✓ Add On Error handling so a missing file or closed Outlook gives a clear message
• ✓ Test each stage on its own before chaining them with the conductor

Comment each stage so future-you knows what it does; save a copy of the workbook before running anything destructive, since macro actions can’t be undone; and consider letting Power Query handle the cleaning stage instead of VBA — it’s more robust for reshaping data, leaving the macro to do the formatting and delivery it’s better at. For a polished version, add basic error handling (On Error) so a missing file or closed Outlook gives a clear message rather than a cryptic crash. Build the stages small, test each alone, chain them with a conductor, and you’ve turned a recurring chore into a one-click pipeline that produces the same clean report every month — which is exactly the kind of work macros were made for.