Automating Reports with VBA: A Practical Guide

Introduction

Manually generating reports in Excel can be time-consuming and prone to errors. With VBA (Visual Basic for Applications), you can automate data processing, formatting, and report generation—saving you time and effort.

In this guide, you’ll learn how to:
✅ Write a VBA macro to automate reports
✅ Extract and process data efficiently
✅ Format reports dynamically
✅ Export reports to PDF or email

Let’s dive in! 🚀

Step 1: Prepare Your Data

Before automating reports, ensure your data is well-structured.

Example Data (Sales Report)

Date	Region	Product	Sales	Profit
01/02/2024	North	Laptop	1200	300
01/03/2024	South	Phone	800	200
01/05/2024	East	Desk	500	150
…	…	…	…	…

📌 Tip: Format your data as a table (CTRL + T) for better handling in VBA.

Step 2: Open the VBA Editor

1️⃣ Open Excel
2️⃣ Press ALT + F11 to launch the VBA Editor
3️⃣ Go to Insert > Module to create a new module

Step 3: Write a VBA Macro to Automate Reports

Now, let’s create a macro that:
✅ Filters data for the current month
✅ Summarizes sales totals
✅ Formats the report

VBA Code for Automating Reports

Sub GenerateMonthlyReport()
    Dim ws As Worksheet, reportWs As Worksheet
    Dim lastRow As Long, reportRow As Long
    Dim currentMonth As String
    
    ' Define data and report sheets
    Set ws = ThisWorkbook.Sheets("SalesData")
    Set reportWs = ThisWorkbook.Sheets("MonthlyReport")
    
    ' Clear previous report
    reportWs.Cells.Clear
    
    ' Set headers for report
    reportWs.Range("A1:D1").Value = Array("Date", "Region", "Product", "Sales")
    reportRow = 2
    
    ' Get last row of data
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    currentMonth = Format(Date, "MM/YYYY")  ' Get current month
    
    ' Loop through data and filter by current month
    Dim i As Long
    For i = 2 To lastRow
        If Format(ws.Cells(i, 1).Value, "MM/YYYY") = currentMonth Then
            reportWs.Cells(reportRow, 1).Resize(1, 4).Value = ws.Cells(i, 1).Resize(1, 4).Value
            reportRow = reportRow + 1
        End If
    Next i
    
    ' Apply formatting
    reportWs.Columns("A:D").AutoFit
    reportWs.Range("A1:D1").Font.Bold = True
    reportWs.Range("A1:D1").Interior.Color = RGB(0, 102, 204) ' Blue header
    
    MsgBox "Monthly Report Generated Successfully!", vbInformation
End Sub

What This Macro Does:

✅ Extracts current month’s data
✅ Summarizes sales in a new sheet
✅ Formats headers and adjusts column width

Step 4: Run the Macro

1️⃣ Go to Developer Tab > Macros
2️⃣ Select GenerateMonthlyReport
3️⃣ Click Run

🎉 Your report is ready! 🎉

📌 Tip: Assign the macro to a button (Developer > Insert > Button) for quick execution.

Step 5: Export Report to PDF

To automate report exporting, modify the macro:

VBA Code to Export Report as PDF:

Sub ExportReportToPDF()
    Dim reportWs As Worksheet
    Dim filePath As String
    
    ' Define report sheet
    Set reportWs = ThisWorkbook.Sheets("MonthlyReport")
    
    ' Set file path
    filePath = ThisWorkbook.Path & "\Monthly_Report_" & Format(Date, "MMYYYY") & ".pdf"
    
    ' Export as PDF
    reportWs.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filePath, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=True
        
    MsgBox "Report saved as PDF!", vbInformation
End Sub

✅ Saves the report as a PDF file
✅ Opens the file automatically

📌 Tip: Modify filePath to save in a specific folder.

Step 6: Automate Report Emailing (Optional)

Want to email the report automatically? Use this VBA code:

VBA Code to Email Report via Outlook

Sub EmailReport()
    Dim OutlookApp As Object, OutlookMail As Object
    Dim filePath As String
    
    ' Define file path
    filePath = ThisWorkbook.Path & "\Monthly_Report_" & Format(Date, "MMYYYY") & ".pdf"
    
    ' Create Outlook Email
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)
    
    ' Configure Email
    With OutlookMail
        .To = "manager@example.com"
        .Subject = "Monthly Sales Report"
        .Body = "Dear Manager, Please find the attached sales report for this month."
        .Attachments.Add filePath
        .Display ' Change to .Send to send automatically
    End With
    
    MsgBox "Email draft created!", vbInformation
End Sub

📌 Tip: Change .Display to .Send to automatically send emails.

Final Steps: Automate Report Scheduling

To run the macro automatically every month, use the Windows Task Scheduler:

1️⃣ Save the macro in Personal Macro Workbook
2️⃣ Create a VBA script to open Excel and run the macro
3️⃣ Schedule it via Task Scheduler to run on the 1st of every month

📌 Tip: You can also use Workbook_Open() event to run the macro when the file is opened.

Conclusion

By automating reports with VBA, you can:
✅ Save time on repetitive tasks
✅ Reduce errors in manual reporting
✅ Deliver insights faster and more efficiently

Master Spreadsheet Like a Pro!