Automate Your Monthly Reports with Excel Macros

Every month, the same ritual: open the report, paste new data, format cells, update headers, copy to distribution list. It takes an hour of mindless clicking. What if one button could do it all? Welcome to Excel macros.

The Problem

Monthly reporting involves repetitive tasks:

Copying data from source systems
Formatting numbers, dates, and headers
Updating titles with current month/year
Calculating summaries and KPIs
Saving with standardized file names
Distributing to stakeholders

Each step is simple, but together they consume hours of valuable time.

The Solution

VBA (Visual Basic for Applications) macros record and automate these tasks. Record once, run forever. We'll build macros that:

Format your report consistently
Update dates and titles automatically
Generate summaries from raw data
Save with dynamic file names
Even send emails with the report attached

What You'll Need

Excel 2013 or later recommended for full VBA functionality (Excel 2010 works but has some limitations)
Developer tab enabled
A report you create monthly
30 minutes to learn, hours saved monthly

Step 1: Enable the Developer Tab

If you don't see the Developer tab:

File > Options
Customize Ribbon
Check "Developer" in the right panel
Click OK

The Developer tab appears with macro tools.

Step 2: Record Your First Macro

Recording captures your actions as VBA code.

Developer > Record Macro
Name it: "FormatReport" (no spaces)
Shortcut key: Ctrl+Shift+F (optional)
Store in: This Workbook
Click OK

Now perform your formatting:

Select header row, apply bold
Set column widths
Apply number formats
Add borders
Set print area

Developer > Stop Recording

Your macro is saved! Press Ctrl+Shift+F (or run from Developer > Macros) to replay it.

Step 3: View and Edit Recorded Code

Developer > Macros
Select your macro
Click Edit

The VBA editor opens with your recorded code:

vba

1Sub FormatReport()
2'
3' FormatReport Macro
4'
5    Rows("1:1").Select
6    Selection.Font.Bold = True
7    Columns("A:A").ColumnWidth = 15
8    Columns("B:B").NumberFormat = "$#,##0.00"
9    ' ... more recorded actions
10End Sub

Understanding the Code:

Sub FormatReport() - Macro name
Rows("1:1").Select - Selects row 1
Selection.Font.Bold = True - Makes selection bold
End Sub - End of macro

Step 4: Write Better Code

Recorded macros work but are inefficient. Here's how to improve them:

Before (Recorded):

vba

1Sub FormatReport()
2    Rows("1:1").Select
3    Selection.Font.Bold = True
4    Selection.Font.Size = 12
5    Columns("B:D").Select
6    Selection.NumberFormat = "$#,##0.00"
7End Sub

After (Optimized):

vba

1Sub FormatReport()
2    With Rows("1:1").Font
3        .Bold = True
4        .Size = 12
5    End With
6    Columns("B:D").NumberFormat = "$#,##0.00"
7End Sub

Key Improvements:

Remove unnecessary .Select and Selection
Use With blocks for multiple properties
Work directly with ranges

Step 5: Create a Complete Report Macro

Here's a practical macro that automates common reporting tasks:

vba

1Sub GenerateMonthlyReport()
2    
3    Dim ws As Worksheet
4    Dim lastRow As Long
5    Dim reportMonth As String
6    
7    ' Reference the active sheet
8    Set ws = ActiveSheet
9    
10    ' Find last row with data
11    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
12    
13    ' Set report month (previous month)
14    reportMonth = Format(DateAdd("m", -1, Date), "MMMM YYYY")
15    
16    ' Update title
17    ws.Range("A1").Value = "Sales Report - " & reportMonth
18    
19    ' Format header row
20    With ws.Range("A3:F3")
21        .Font.Bold = True
22        .Font.Size = 11
23        .Interior.Color = RGB(0, 112, 192)
24        .Font.Color = RGB(255, 255, 255)
25    End With
26    
27    ' Format data range
28    With ws.Range("A4:F" & lastRow)
29        .Borders.LineStyle = xlContinuous
30        .Borders.Weight = xlThin
31    End With
32    
33    ' Format currency columns
34    ws.Range("D4:F" & lastRow).NumberFormat = "$#,##0.00"
35    
36    ' Format date column
37    ws.Range("B4:B" & lastRow).NumberFormat = "MM/DD/YYYY"
38    
39    ' Auto-fit columns
40    ws.Columns("A:F").AutoFit
41    
42    ' Add summary row
43    ws.Cells(lastRow + 2, 1).Value = "TOTAL"
44    ws.Cells(lastRow + 2, 4).Formula = "=SUM(D4:D" & lastRow & ")"
45    ws.Cells(lastRow + 2, 5).Formula = "=SUM(E4:E" & lastRow & ")"
46    ws.Cells(lastRow + 2, 6).Formula = "=SUM(F4:F" & lastRow & ")"
47    
48    ' Format total row
49    With ws.Range("A" & lastRow + 2 & ":F" & lastRow + 2)
50        .Font.Bold = True
51        .Interior.Color = RGB(221, 235, 247)
52    End With
53    
54    MsgBox "Report generated for " & reportMonth, vbInformation, "Complete"
55    
56End Sub

Step 6: Save the Workbook with Dynamic Name

Add this to your macro or create a separate one:

vba

1Sub SaveReport()
2    
3    Dim filePath As String
4    Dim fileName As String
5    Dim reportMonth As String
6    
7    ' Create file name with current date
8    reportMonth = Format(DateAdd("m", -1, Date), "YYYY-MM")
9    fileName = "Sales_Report_" & reportMonth & ".xlsx"
10    
11    ' Set save location (modify path as needed)
12    filePath = "C:\Reports\" & fileName
13    
14    ' Save as new file
15    ActiveWorkbook.SaveAs fileName:=filePath, _
16        FileFormat:=xlOpenXMLWorkbook
17    
18    MsgBox "Report saved as: " & fileName, vbInformation, "Saved"
19    
20End Sub

Step 7: Create a Button to Run Macros

Insert a Button:

Developer > Insert > Button (Form Control)
Draw the button on your sheet
Assign Macro dialog appears
Select your macro
Click OK
Right-click button to edit text: "Generate Report"

Or use a Shape:

Insert > Shapes > Rectangle
Format and add text
Right-click > Assign Macro

The Complete Solution

Here's a master macro that ties everything together:

vba

1Sub ProcessMonthlyReport()
2    
3    Application.ScreenUpdating = False
4    Application.Calculation = xlCalculationManual
5    
6    ' Step 1: Clear old data
7    Call ClearPreviousData
8    
9    ' Step 2: Import new data
10    Call ImportDataFromSource
11    
12    ' Step 3: Format the report
13    Call FormatReport
14    
15    ' Step 4: Generate summaries
16    Call CreateSummarySection
17    
18    ' Step 5: Save the file
19    Call SaveReport
20    
21    Application.Calculation = xlCalculationAutomatic
22    Application.ScreenUpdating = True
23    
24    MsgBox "Monthly report processing complete!", vbInformation
25    
26End Sub
27
28Sub ClearPreviousData()
29    ' Clear data area (keep headers)
30    Dim ws As Worksheet
31    Set ws = Sheets("Data")
32    
33    Dim lastRow As Long
34    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
35    
36    If lastRow > 3 Then
37        ws.Range("A4:Z" & lastRow).ClearContents
38    End If
39End Sub
40
41Sub ImportDataFromSource()
42    ' Example: Import from CSV
43    Dim sourceFile As String
44    sourceFile = "C:\Data\monthly_export.csv"
45    
46    ' Check if file exists
47    If Dir(sourceFile) = "" Then
48        MsgBox "Source file not found: " & sourceFile, vbExclamation
49        Exit Sub
50    End If
51    
52    ' Import the data
53    With ActiveSheet.QueryTables.Add( _
54        Connection:="TEXT;" & sourceFile, _
55        Destination:=Range("A4"))
56        .TextFileCommaDelimiter = True
57        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
58        .Refresh BackgroundQuery:=False
59        .Delete
60    End With
61End Sub
62
63Sub CreateSummarySection()
64    Dim ws As Worksheet
65    Dim summaryWs As Worksheet
66    Dim lastRow As Long
67    
68    Set ws = Sheets("Data")
69    Set summaryWs = Sheets("Summary")
70    
71    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
72    
73    ' Create summary by category using formulas
74    summaryWs.Range("B2").Formula = _
75        "=SUMIF(Data!C:C,A2,Data!D:D)"
76End Sub

Macro Architecture

Macro	Purpose
ProcessMonthlyReport	Master controller
ClearPreviousData	Remove old data
ImportDataFromSource	Bring in new data
FormatReport	Apply formatting
CreateSummarySection	Build summaries
SaveReport	Save with dynamic name

Common Variations

Variation 1: Email the Report

vba

1Sub EmailReport()
2    Dim OutApp As Object
3    Dim OutMail As Object
4    
5    Set OutApp = CreateObject("Outlook.Application")
6    Set OutMail = OutApp.CreateItem(0)
7    
8    With OutMail
9        .To = "manager@company.com"
10        .Subject = "Monthly Report - " & Format(Date, "MMMM YYYY")
11        .Body = "Please find the monthly report attached."
12        .Attachments.Add ActiveWorkbook.FullName
13        .Display  ' Change to .Send to send automatically
14    End With
15    
16    Set OutMail = Nothing
17    Set OutApp = Nothing
18End Sub

Variation 2: Loop Through Multiple Sheets

vba

1Sub FormatAllSheets()
2    Dim ws As Worksheet
3    
4    For Each ws In ActiveWorkbook.Worksheets
5        If ws.Name <> "Summary" Then
6            ws.Activate
7            Call FormatReport
8        End If
9    Next ws
10End Sub

Variation 3: Error Handling

vba

1Sub SafeProcess()
2    On Error GoTo ErrorHandler
3    
4    ' Your code here
5    Call ProcessMonthlyReport
6    
7    Exit Sub
8    
9ErrorHandler:
10    MsgBox "Error: " & Err.Description, vbCritical
11    Application.ScreenUpdating = True
12    Application.Calculation = xlCalculationAutomatic
13End Sub

Pro Tips

Use Option Explicit: Add at top of module to require variable declarations
Turn off screen updating: Application.ScreenUpdating = False speeds execution
Disable calculation: Application.Calculation = xlCalculationManual during data changes
Comment your code: Future you will thank present you
Save as .xlsm: Macro-enabled workbooks require this format

Troubleshooting

Issue	Cause	Solution
Macros disabled	Security settings	Enable in Trust Center
"Sub or Function not defined"	Typo in macro name	Check spelling
Runtime error	Code bug	Debug with F8 (step through)
Macro not saved	Saved as .xlsx	Save as .xlsm
Slow execution	Screen updating on	Add ScreenUpdating = False

Security Considerations

Macro Security Settings:

File > Options > Trust Center > Trust Center Settings
Macro Settings > Choose appropriate level

Options:

Disable all macros with notification (recommended)
Enable macros only in trusted locations
Never enable macros from untrusted sources

Digital Signatures: For distribution, sign your macros with a certificate to verify authenticity.

Debugging Your Macros

F8 - Step Through: In VBA editor, press F8 to execute one line at a time. Watch variables change.

Breakpoints: Click in the gray margin to add a breakpoint. Code pauses there during execution.

Immediate Window: Ctrl+G opens Immediate Window. Type ? variableName to see current value.

Watch Window: Add variables to Watch Window to monitor their values as code runs.

Conclusion

Macros transform tedious monthly routines into one-click operations. The hour you spend building a macro pays back every month thereafter. Start simple—record a formatting macro. Then learn to edit and improve it. Before long, you'll be building sophisticated automation that handles entire workflows.

The best part? Unlike manual processes, macros are consistent. They don't forget steps, don't make typos, and don't get tired on Friday afternoons. Your reports will be accurate and formatted identically every time.

Take that monthly report that eats two hours of your time. Automate it. Reclaim those hours for work that actually needs human intelligence.

Let the machine do the machine work.