Generate Customized Reports Automatically With Excel VBA

The goal here is to automate the generation of reports using Excel VBA. We will create a customized report that pulls data from a source sheet, processes it, and then formats it into a report sheet.

Objective

We will:

• Extract data from an existing worksheet (such as raw data or a data source).
• Apply specific filters to select relevant information.
• Generate a customized report with headers, calculated values, and formatting.
• Export the report to a new worksheet or a new file.

Detailed Explanation

1. Data Structure:
   • Assume we have a worksheet named « DataSource » with columns such as « Date », « Product », « Sales », « Region », etc.
   • We will generate a report that groups sales data by « Region » and « Product » and calculates the total sales for each region-product combination.
2. VBA Setup:
   • Open the Excel workbook.
   • Press Alt + F11 to open the VBA editor.
   • Insert a new module by clicking on Insert > Module.
   • Write the VBA code in this module to automate the report generation.
3. Process Flow:
   • Step 1: Collect data from the « DataSource ».
   • Step 2: Filter and group the data.
   • Step 3: Create a new sheet for the report.
   • Step 4: Write headers and format the report.
   • Step 5: Calculate summary statistics.
   • Step 6: Format the final report.

VBA Code to Generate Customized Reports Automatically

Sub GenerateCustomizedReport()
    ' Define variables
    Dim wsSource As Worksheet
    Dim wsReport As Worksheet
    Dim lastRow As Long
    Dim reportRow As Long
    Dim region As String
    Dim product As String
    Dim totalSales As Double
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary"
    ' Set reference to the source data sheet
    Set wsSource = ThisWorkbook.Sheets("DataSource")
    ' Get the last row with data in the DataSource sheet
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    ' Create a new sheet for the report
    Set wsReport = ThisWorkbook.Sheets.Add
    wsReport.Name = "CustomizedReport"
    ' Write headers in the report sheet
    wsReport.Cells(1, 1).Value = "Region"
    wsReport.Cells(1, 2).Value = "Product"
    wsReport.Cells(1, 3).Value = "Total Sales"
    ' Start writing data from row 2
    reportRow = 2
    ' Loop through the source data to process sales by region and product
    For i = 2 To lastRow ' Assume row 1 is headers
        region = wsSource.Cells(i, 4).Value ' Column 4 = "Region"
        product = wsSource.Cells(i, 2).Value ' Column 2 = "Product"
        totalSales = wsSource.Cells(i, 3).Value ' Column 3 = "Sales"
        ' Create a unique key combining region and product
        Dim key As String
        key = region & "-" & product
        ' If the key doesn't exist in the dictionary, add it with initial value
        If Not dict.Exists(key) Then
            dict.Add key, totalSales
        Else
            ' If the key exists, add the sales to the existing value
            dict(key) = dict(key) + totalSales
        End If
    Next i
    ' Loop through the dictionary and write data into the report sheet
    For Each key In dict.Keys
        ' Split the key into region and product
        Dim keyParts() As String
        keyParts = Split(key, "-")       
        ' Write region, product, and total sales to the report sheet
        wsReport.Cells(reportRow, 1).Value = keyParts(0)
        wsReport.Cells(reportRow, 2).Value = keyParts(1)
        wsReport.Cells(reportRow, 3).Value = dict(key)     
        reportRow = reportRow + 1
    Next key
    ' Apply some formatting to the report
    With wsReport
        ' Format the header row
        .Rows(1).Font.Bold = True
        .Rows(1).HorizontalAlignment = xlCenter
        .Rows(1).Interior.Color = RGB(200, 200, 255)
        ' Adjust column widths
        .Columns("A:C").AutoFit
        ' Add a border around the data range
        With .Range("A1:C" & reportRow - 1).Borders
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        ' Add a total sales sum at the bottom
        .Cells(reportRow, 1).Value = "Total"
        .Cells(reportRow, 2).Value = "Sales"
        .Cells(reportRow, 3).Formula = "=SUM(C2:C" & reportRow - 1 & ")"
        .Cells(reportRow, 3).Font.Bold = True
    End With
    ' Notify the user the report is generated
    MsgBox "Customized report generated successfully!", vbInformation
End Sub

Explanation of the Code

1. Variables:
   • wsSource: Refers to the source worksheet (where raw data is stored).
   • wsReport: The new worksheet where the report will be created.
   • lastRow: The last row in the source data sheet.
   • dict: A dictionary object to store and sum the sales data by « Region » and « Product ».
   • key: A unique key created by combining the region and product to ensure the data is grouped correctly.
2. Creating a New Worksheet:
   • A new worksheet is created with the name « CustomizedReport ».
3. Looping Through Data:
   • The code loops through each row in the « DataSource » worksheet and creates a unique key combining the region and product.
   • The sales value for each region-product combination is stored and accumulated in the dictionary.
4. Writing Report Data:
   • After processing all the data, the report is generated by iterating over the dictionary and writing the region, product, and total sales for each combination to the new report sheet.
5. Formatting:
   • The report is formatted with bold headers, centered text, and a light background color.
   • The columns are auto-fitted for better presentation.
   • Borders are added around the data for clarity.
   • A sum of total sales is calculated at the bottom of the report.
6. User Notification:
   • After the report is generated, a message box is shown to notify the user that the report has been successfully created.

Running the VBA Code

1. Open Excel and press Alt + F11 to open the VBA editor.
2. Insert a new module via Insert > Module.
3. Copy and paste the code above into the module.
4. Press F5 to run the code, and it will generate the customized report.

Customization

You can adjust this code to suit your specific needs:

• Modify the columns and data references based on the structure of your source data.
• Apply additional formatting such as conditional formatting, different fonts, or color schemes.
• Use more complex calculations (e.g., averages, percentage growth) depending on your reporting requirements.

This is a basic template that can be expanded for more complex scenarios, such as adding charts or exporting the report to a PDF file.