5 Excel VBA Macros Every Small Business Should Use in 2026

If your team is still manually formatting reports, copying data between sheets, or sending the same email attachments every Monday, you are losing hours every week to tasks that Excel can handle automatically. VBA macros are the most powerful free tool built into Microsoft Excel — and most small business owners have never used a single one.

In this guide you will find 5 ready-to-use Excel VBA macros small business owners can install in under 10 minutes. Each macro comes with the complete code, plain-English explanation and step-by-step installation instructions. No programming experience required.

What is a VBA Macro and Why Does Your Business Need One?

VBA (Visual Basic for Applications) is a built-in programming language in Microsoft Excel that allows you to automate repetitive tasks. A macro is simply a recorded or written set of instructions that Excel follows automatically when you press a button or open a file.

Think of a macro as a robot assistant living inside your spreadsheet. You tell it what to do once, and it does that exact thing instantly every time you need it — whether that is formatting a report, generating an invoice or sending an email.

The Difference Between a Formula and a Macro

Formulas calculate values — they answer questions like “what is the total revenue this month?” Macros perform actions — they do things like “format this report, save it as PDF and email it to the finance team.” Formulas are passive. Macros are active. Both are powerful but macros can automate entire workflows that formulas cannot touch.

How Much Time Can a Macro Actually Save?

For a typical small business running weekly reports, the five macros in this guide save an average of four to eight hours per week. Over a full year that is over 200 hours — equivalent to five full working weeks returned to your business at zero cost.

Macro 1 — Auto-Format and Send Weekly Reports by Email

What This Macro Does

This macro takes your weekly data sheet, applies consistent professional formatting — bold headers, alternating row colours, auto-fit columns — then saves a copy as PDF and emails it to a defined list of recipients automatically. What previously took 25 minutes now takes 3 seconds.

The Code (Copy and Paste Ready)

Sub AutoFormatAndEmailReport()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    
    Set ws = ThisWorkbook.Sheets("WeeklyReport")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' Auto-format the sheet
    With ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
        .Borders.LineStyle = xlContinuous
        .Borders.Weight = xlThin
    End With
    
    ' Bold header row
    ws.Rows(1).Font.Bold = True
    ws.Rows(1).Interior.Color = RGB(10, 37, 64)
    ws.Rows(1).Font.Color = RGB(255, 255, 255)
    
    ' Auto-fit columns
    ws.Columns.AutoFit
    
    ' Save as PDF
    Dim pdfPath As String
    pdfPath = Environ("USERPROFILE") & "\Desktop\WeeklyReport.pdf"
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath
    
    ' Send email via Outlook
    Dim outlookApp As Object
    Dim outlookMail As Object
    Set outlookApp = CreateObject("Outlook.Application")
    Set outlookMail = outlookApp.CreateItem(0)
    
    With outlookMail
        .To = "team@yourbusiness.com"
        .Subject = "Weekly Report - " & Format(Date, "DD MMM YYYY")
        .Body = "Please find this week's report attached."
        .Attachments.Add pdfPath
        .Send
    End With
    
    MsgBox "Report formatted and sent successfully!", vbInformation
End Sub

How to Install This Macro in Excel

1. Open your Excel file and press Alt + F11 to open the VBA Editor
2. In the left panel right-click your workbook name and select Insert → Module
3. Paste the code above into the empty module window
4. Change “WeeklyReport” to your actual sheet name
5. Change “team@yourbusiness.com” to your recipient email
6. Press F5 to run or close the editor and assign it to a button
7. Save your file as .xlsm format (macro-enabled workbook)

Macro 2 — Auto-Generate Invoices From a Data List

What This Macro Does

This macro reads a list of clients, services and amounts from a data sheet, automatically fills a pre-designed invoice template for each client, saves each invoice as a separate PDF file named after the client, and stores them in a designated folder. Generating 20 invoices that used to take an hour now takes under 60 seconds.

The Code

Sub GenerateInvoices()
    Dim dataSheet As Worksheet
    Dim invoiceSheet As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim savePath As String
    
    Set dataSheet = ThisWorkbook.Sheets("ClientData")
    Set invoiceSheet = ThisWorkbook.Sheets("InvoiceTemplate")
    lastRow = dataSheet.Cells(dataSheet.Rows.Count, 1).End(xlUp).Row
    savePath = Environ("USERPROFILE") & "\Desktop\Invoices\"
    
    ' Create Invoices folder if it doesn't exist
    If Dir(savePath, vbDirectory) = "" Then
        MkDir savePath
    End If
    
    ' Loop through each client row
    For i = 2 To lastRow
        ' Copy data to invoice template
        invoiceSheet.Range("B5").Value = dataSheet.Cells(i, 1).Value  ' Client Name
        invoiceSheet.Range("B6").Value = dataSheet.Cells(i, 2).Value  ' Service
        invoiceSheet.Range("B7").Value = dataSheet.Cells(i, 3).Value  ' Amount
        invoiceSheet.Range("B8").Value = Date  ' Invoice Date
        invoiceSheet.Range("B9").Value = "INV-" & Format(i, "0000")  ' Invoice Number
        
        ' Save as PDF
        Dim fileName As String
        fileName = savePath & dataSheet.Cells(i, 1).Value & "_Invoice.pdf"
        invoiceSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fileName
        
    Next i
    
    MsgBox lastRow - 1 & " invoices generated successfully!", vbInformation
End Sub

How to Use It

Create a sheet named ClientData with columns for Client Name, Service and Amount. Create a second sheet named InvoiceTemplate with your branded invoice design. Run the macro and all invoices are generated and saved to your Desktop in a folder called Invoices. Update the cell references to match your actual template layout.

Macro 3 — Clean and Standardise Imported Data Automatically

What This Macro Does

When you import data from CRM systems, accounting software or online stores, it often arrives with inconsistent capitalisation, extra spaces, duplicate rows and mixed date formats. This macro cleans all of it automatically — removing duplicates, trimming spaces, standardising text case and fixing number formats in seconds.

The Code

Sub CleanImportedData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim cell As Range
    Dim i As Long
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' Remove leading and trailing spaces from all cells
    For Each cell In ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, lastCol))
        If Not IsEmpty(cell) Then
            If VarType(cell.Value) = vbString Then
                cell.Value = Trim(cell.Value)
                ' Proper case for text columns
                cell.Value = StrConv(cell.Value, vbProperCase)
            End If
        End If
    Next cell
    
    ' Remove duplicate rows
    ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).RemoveDuplicates _
        Columns:=1, Header:=xlYes
    
    ' Auto-fit all columns
    ws.Columns.AutoFit
    
    MsgBox "Data cleaned successfully! Duplicates removed.", vbInformation
End Sub

When to Use This Macro

Run this macro immediately after importing any data from an external source. It is especially useful for businesses that import customer lists from CRM exports, product data from supplier spreadsheets, or transaction records from accounting software. Run it before doing any analysis or reporting to ensure your data is clean and consistent.

Macro 4 — Highlight Overdue Payments and Deadlines

What This Macro Does

This macro scans a column of due dates and automatically highlights overdue items in red, items due within 7 days in orange, and items on track in green. It updates every time you run it so your payment tracker or project deadline sheet always shows accurate status at a glance without manual colour-coding.

The Code

Sub HighlightOverdueItems()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim dueDate As Date
    Dim today As Date
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row
    today = Date
    
    ' Column C = Due Date column (change to match your sheet)
    For i = 2 To lastRow
        If Not IsEmpty(ws.Cells(i, 3)) Then
            If IsDate(ws.Cells(i, 3).Value) Then
                dueDate = CDate(ws.Cells(i, 3).Value)
                
                If dueDate < today Then
                    ' Overdue - Red
                    ws.Rows(i).Interior.Color = RGB(255, 199, 199)
                    ws.Cells(i, 4).Value = "OVERDUE"
                ElseIf dueDate <= today + 7 Then
                    ' Due soon - Orange
                    ws.Rows(i).Interior.Color = RGB(255, 235, 156)
                    ws.Cells(i, 4).Value = "DUE SOON"
                Else
                    ' On track - Green
                    ws.Rows(i).Interior.Color = RGB(198, 239, 206)
                    ws.Cells(i, 4).Value = "ON TRACK"
                End If
            End If
        End If
    Next i
    
    MsgBox "Payment status updated successfully!", vbInformation
End Sub

How to Customise the Date Range

Change today + 7 to any number of days that suits your business. For example use today + 14 for a two-week warning window or today + 30 for monthly payment cycles. Change the column number 3 to match whichever column contains your due dates.

Macro 5 — Create Monthly Summary Sheets Automatically

What This Macro Does

This macro automatically creates a new summary sheet for each month, copies the relevant data from your master sheet, adds totals and a simple chart, and names each sheet with the month and year. Instead of manually creating 12 summary sheets per year, this macro builds them all in seconds.

The Code

Sub CreateMonthlySummary()
    Dim wb As Workbook
    Dim masterSheet As Worksheet
    Dim summarySheet As Worksheet
    Dim monthName As String
    Dim currentMonth As Integer
    Dim currentYear As Integer
    
    Set wb = ThisWorkbook
    Set masterSheet = wb.Sheets("MasterData")
    
    currentMonth = Month(Date)
    currentYear = Year(Date)
    monthName = Format(Date, "MMMM YYYY")
    
    ' Check if sheet already exists
    Dim sheetExists As Boolean
    sheetExists = False
    Dim ws As Worksheet
    For Each ws In wb.Worksheets
        If ws.Name = monthName Then
            sheetExists = True
            Exit For
        End If
    Next ws
    
    If sheetExists Then
        MsgBox monthName & " summary already exists!", vbInformation
        Exit Sub
    End If
    
    ' Create new summary sheet
    Set summarySheet = wb.Worksheets.Add(After:=wb.Sheets(wb.Sheets.Count))
    summarySheet.Name = monthName
    
    ' Copy headers from master sheet
    masterSheet.Rows(1).Copy Destination:=summarySheet.Rows(1)
    
    ' Copy current month data
    Dim lastRow As Long
    Dim i As Long
    Dim destRow As Long
    lastRow = masterSheet.Cells(masterSheet.Rows.Count, 1).End(xlUp).Row
    destRow = 2
    
    For i = 2 To lastRow
        If Month(masterSheet.Cells(i, 1).Value) = currentMonth And _
           Year(masterSheet.Cells(i, 1).Value) = currentYear Then
            masterSheet.Rows(i).Copy Destination:=summarySheet.Rows(destRow)
            destRow = destRow + 1
        End If
    Next i
    
    ' Format summary sheet
    summarySheet.Rows(1).Font.Bold = True
    summarySheet.Columns.AutoFit
    
    MsgBox monthName & " summary created successfully!", vbInformation
End Sub

How to Schedule This Macro to Run Automatically

To run this macro automatically on the first day of each month, add this code to the ThisWorkbook module in the VBA editor:

Private Sub Workbook_Open()
    If Day(Date) = 1 Then
        Call CreateMonthlySummary
    End If
End Sub

Now every time you open the workbook on the first of the month the summary sheet for that month is created automatically.

How to Enable Macros in Excel Safely

Before you can run any macro you need to enable macros in Excel. Here is how to do it safely:

1. Open Excel and click File → Options
2. Click Trust Center → Trust Center Settings
3. Click Macro Settings
4. Select "Disable all macros with notification"
5. Click OK
6. When you open a macro-enabled file Excel will show a yellow security bar — click Enable Content
7. Always save macro files as .xlsm not .xlsx

Common VBA Macro Mistakes Small Businesses Make

Mistake 1 — Not Saving as XLSM Format

If you save your macro workbook as a regular .xlsx file Excel will delete all your macros without warning. Always save as .xlsm (Excel Macro-Enabled Workbook). You will see this option in the Save As dialog under File Format.

Mistake 2 — Hardcoding File Paths

Writing a specific file path like C:\Users\John\Desktop\report.xlsx in your macro means it will fail on any other computer. Always use Environ("USERPROFILE") to reference the current user's profile folder — this works on any Windows computer regardless of username.

Mistake 3 — No Error Handling

Macros without error handling crash with an unhelpful error message when something unexpected happens. Add On Error GoTo ErrorHandler at the start of your macros so they fail gracefully with a clear message rather than confusing your team with a VBA error dialog.

When to Hire a VBA Specialist Instead

The five macros above cover the most common small business automation needs. However some situations genuinely need specialist expertise:

• Macros that connect Excel to external databases or APIs
• Automated reporting systems pulling from multiple workbooks simultaneously
• Custom UserForms for data entry with validation rules
• Integration between Excel and ERP systems like SAP or Odoo
• Macros that interact with other Office applications like Word or Outlook at scale

At DataAdminPro we build custom Excel automation solutions including VBA macros for small businesses in the USA, UK and Australia. We analyse your current manual processes, identify every task that can be automated, and deliver working macros in one to three business days. Fixed pricing starts from $25.

Frequently Asked Questions

Is VBA difficult to learn for a beginner?

VBA is one of the more approachable programming languages because it uses plain English words and works within Excel which most business users already know. The macros in this guide are fully ready to use without any prior coding knowledge — just copy paste and adjust the sheet names and email addresses.

Will these macros work in Excel 365?

Yes. All five macros in this guide are compatible with Excel 2016, Excel 2019, Excel 2021 and Excel 365. They also work on Windows and Mac versions of Excel though the email macro requires Microsoft Outlook to be installed on Windows.

How do I run a macro in Excel?

Press Alt + F8 to open the Macro dialog box, select the macro name from the list and click Run. Alternatively you can assign any macro to a button on your spreadsheet by going to Developer tab, inserting a button and assigning the macro to it.

Can macros damage my Excel file?

Macros written by you or a trusted specialist cannot damage your files. Always keep a backup copy of your workbook before running a new macro for the first time. Never enable macros in files received from unknown sources as malicious macros do exist — only use macros from trusted sources.

How much does it cost to hire someone to write VBA macros?

At DataAdminPro our Excel VBA macro projects start from $25 for simple single-task macros. Complex multi-workbook automation systems are priced based on scope. All work is fixed price — no hourly billing surprises and no payment until you are satisfied.

Final Thoughts

VBA macros are one of the most underused tools in small business Excel. Every macro in this guide is free to use, takes under 10 minutes to install and will save your business hours every single week from the moment you set it up.

Start with Macro 1 or Macro 3 — whichever matches your biggest time drain right now. Get it working, see the time savings, and then add the others one by one. If you hit any issues or need a custom macro built specifically for your business workflow, contact DataAdminPro for a free consultation.