VBA Excel: The Complete Guide to Automating Macros

Introduction: Why VBA is Still Relevant

Even in the age of AI, Copilot, Python, and Power Automate, VBA (Visual Basic for Applications) remains unparalleled for automating small to medium internal tasks within classic Excel workbooks. Why? Because it runs completely locally, it does not require external licenses, and every user can run a macro instantly by clicking a button.

If you waste 5 hours a week copying and pasting data, formatting sheets, and generating PDFs, VBA can completely automate your workload.

1. Getting Started: The Developer Tab

Before writing any code, you need to enable the Developer tab:

1. Go to File > Options > Customize Ribbon.
2. Check the “Developer” box.
3. Click OK.

Now, open the VBA Editor by pressing Alt + F11.

2. Macro 1: Automatically Clean and Format Data

Does your finance department always send you unformatted CSVs? Let’s fix that with one click.

Sub FormatReport()
    ' Auto-fit all columns
    Cells.EntireColumn.AutoFit
    
    ' Bold the first row (headers)
    Rows("1:1").Font.Bold = True
    
    ' Add a light background color to headers
    Rows("1:1").Interior.Color = RGB(200, 220, 240)
    
    ' Format column C as Currency
    Columns("C:C").NumberFormat = "$#,##0.00"
    
    MsgBox "Report Formatted Successfully!", vbInformation
End Sub

3. Macro 2: Generate PDFs and Send via Outlook

One of the most powerful uses of VBA is chaining multiple Microsoft applications together. You can generate a PDF report from an Excel sheet and automatically draft an email in Outlook.

Sub SendReportViaOutlook()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim pdfPath As String
    
    ' 1. Export the active sheet as a PDF
    pdfPath = Environ("USERPROFILE") & "\Desktop\Monthly_Report.pdf"
    
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=pdfPath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False
    
    ' 2. Open Outlook and attach the PDF
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    With OutMail
        .To = "boss@company.com"
        .Subject = "Monthly KPI Report"
        .Body = "Hello," & vbCrLf & vbCrLf & "Please find attached the KPIs for this month." & vbCrLf & vbCrLf & "Regards,"
        .Attachments.Add pdfPath
        .Display ' Change to .Send to send it instantly without reviewing
    End With
    
    ' Cleanup
    Set OutMail = Nothing
    Set OutApp = Nothing
    
    MsgBox "Email created successfully!", vbInformation
End Sub

4. Macro 3: Combine Multiple Sheets into One Master Sheet

If you have 12 sheets (one for each month) and you want to consolidate them all onto a master summary sheet, a simple loop will do the trick.

Sub ConsolidateSheets()
    Dim ws As Worksheet
    Dim destSheet As Worksheet
    Dim lastRow As Long
    Dim destRow As Long
    
    ' Create or clear the Master sheet
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Master").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    Set destSheet = Sheets.Add
    destSheet.Name = "Master"
    
    destRow = 2 ' Leave row 1 for headers
    
    ' Loop through every sheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Master" Then
            lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
            
            ' Copy data and paste into Master sheet
            ws.Range("A2:F" & lastRow).Copy destSheet.Range("A" & destRow)
            
            ' Update the destination row
            destRow = destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Row + 1
        End If
    Next ws
    
    MsgBox "All sheets combined!", vbInformation
End Sub

Conclusion: Stop Doing the Robot’s Job

VBA might look like code from the 90s, but its ability to interact instantly with the entire Microsoft Office suite makes it irreplaceable for operational business logic.

👉 Want to automate your daily tasks? Explore our VBA & Excel Automation Course and learn how to write robust, error-free macros.