How to Automate Your Daily Reports
with VBA Macros

🖨️ Print / Save as PDF

Are you spending 30–60 minutes every day creating the same report — copying data, formatting cells, applying filters, and saving files? If yes, VBA Macros can give you that time back.

VBA (Visual Basic for Applications) is Excel's built-in programming language. With it, you can record and automate any repetitive task — and run it with a single click. You do not need to be a programmer to get started.

In this article, you will learn — step by step — how to record your first macro, understand the code it generates, edit it, and use it to automate your daily reports.

What is a VBA Macro?

A Macro is a recorded sequence of actions in Excel. When you record a macro, Excel watches what you do and writes the VBA code for you automatically. You can then replay that recorded sequence any time — instantly.

Think of it like this: instead of doing 20 steps manually every morning, you press one button and Excel does all 20 steps for you in under 3 seconds.

Before You Start — Enable the Developer Tab

The Developer tab is hidden by default. You need it to record and run macros.

Step 1 — Record Your First Macro

The fastest way to create a macro is to simply record what you do. Excel writes the code for you.

Step 2 — View and Understand the VBA Code

After recording, Excel generates VBA code automatically. Let us open it and understand what it looks like.

Here is what a typical recorded macro looks like — let us say you recorded formatting a header row:

' This macro formats the header row of your report Sub FormatDailyReport() ' Select the header row Range("A1:F1").Select ' Apply bold formatting Selection.Font.Bold = True ' Set background colour to red Selection.Interior.Color = RGB(239, 68, 68) ' Set font colour to white Selection.Font.Color = RGB(255, 255, 255) ' Set font size to 12 Selection.Font.Size = 12 ' Auto-fit all columns Cells.EntireColumn.AutoFit End Sub

Every macro starts with Sub MacroName() and ends with End Sub. Everything in between is the list of instructions Excel follows.

Step 3 — Write a Macro from Scratch

Now let us write a real, practical macro manually. This one will automate a daily report — it will format data, add a date stamp, and save a copy automatically.

Sub AutomateDailyReport() '--- Step 1: Format the header row --- With Range("A1:F1") .Font.Bold = True .Font.Size = 12 .Font.Color = RGB(255, 255, 255) .Interior.Color = RGB(239, 68, 68) .HorizontalAlignment = xlCenter End With '--- Step 2: Auto-fit all columns --- Cells.EntireColumn.AutoFit '--- Step 3: Add today's date in H1 --- Range("H1").Value = "Report Date: " & Format(Now(), "DD-MMM-YYYY") Range("H1").Font.Bold = True '--- Step 4: Sort by Sales column (B) highest first --- Dim lastRow As Long lastRow = Cells(Rows.Count, "A").End(xlUp).Row Range("A1:F" & lastRow).Sort _ Key1:=Range("B1"), _ Order1:=xlDescending, _ Header:=xlYes '--- Step 5: Save a copy with today's date --- Dim savePath As String savePath = ThisWorkbook.Path & "\" & _ "DailyReport_" & Format(Now(), "YYYY-MM-DD") & ".xlsx" ThisWorkbook.SaveCopyAs savePath '--- Step 6: Show confirmation message --- MsgBox "✅ Report ready! Saved as: " & savePath, _ vbInformation, "Daily Report Done" End Sub

Step 4 — Run Your Macro

There are 4 ways to run a macro in Excel:

Step 5 — Add a Button to Run the Macro

Step 6 — Loop Through Data Automatically

One of VBA's most powerful features is looping — going through each row and doing something. Here is a practical loop that highlights rows where sales are below target:

Sub HighlightBelowTarget() Dim i As Long Dim lastRow As Long Dim target As Long target = 50000 ' Set your sales target here lastRow = Cells(Rows.Count, "A").End(xlUp).Row ' Loop through each data row (starting from row 2) For i = 2 To lastRow If Cells(i, 2).Value < target Then ' Highlight the entire row in light red Rows(i).Interior.Color = RGB(255, 220, 220) Else ' Clear any existing colour for rows above target Rows(i).Interior.ColorIndex = xlNone End If Next i MsgBox "Done! Rows below ₹50,000 target are highlighted.", _ vbInformation End Sub

Step 7 — Schedule Your Macro to Run Automatically

You can make a macro run automatically when you open the workbook — so your report is ready the moment you open the file every morning.

' This goes in the "ThisWorkbook" module — NOT in a Module ' Double-click "ThisWorkbook" in the VBA editor to find it Private Sub Workbook_Open() ' This runs automatically every time the file is opened Call AutomateDailyReport End Sub

Common VBA Mistakes to Avoid

1. Saving as .xlsx instead of .xlsm — All macros are deleted. Always save as .xlsm.
2. Using Select and Selection everywhere — Recorded macros use Select a lot. Professional VBA avoids it. Instead of Range("A1").Select / Selection.Bold = True, write Range("A1").Bold = True directly.
3. Hardcoding the last row number — Never write Range("A1:A500"). Use Cells(Rows.Count,"A").End(xlUp).Row to find it dynamically.
4. Not handling errors — If something goes wrong, the macro crashes. Add On Error GoTo ErrorHandler at the top of important macros.
5. No comments in code — Always add comments (lines starting with ') to explain what each section does. Future you will thank present you.