Automate data summarization tasks in Excel using VBA

Automating data summarization tasks in Excel using VBA (Visual Basic for Applications) is an excellent way to save time and increase efficiency. The goal is to centralize data, summarize or analyze it based on specific criteria, and generate reports or charts automatically.

Example: Automating Data Summarization with VBA

Let’s assume you have a sales data sheet, and you want to create a summary that calculates total sales by product, region, and month.

Here’s a detailed VBA code example for automating this process:

1. Data Preparation

Assume you have a data sheet structured as follows (named « Data »):

2. Objective

The objective is to create an automatic summary that:

• Calculates total sales by product.
• Calculates total sales by region.
• Calculates total sales by month.

We will write a VBA code to generate this summary in a new sheet.

3. VBA Code to Automate Data Summarization

Here’s the detailed VBA code:

Sub SalesSummary()
    ' Variable declarations
    Dim wsSource As Worksheet
    Dim wsSummary As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim dictProducts As Object
    Dim dictRegions As Object
    Dim dictMonths As Object
    Dim monthDate As String
    Dim product As String
    Dim region As String
    Dim sales As Double   
    ' Initialize dictionaries for calculations
    Set dictProducts = CreateObject("Scripting.Dictionary")
    Set dictRegions = CreateObject("Scripting.Dictionary")
    Set dictMonths = CreateObject("Scripting.Dictionary")   
    ' References to the sheets
    Set wsSource = ThisWorkbook.Sheets("Data")
    On Error Resume Next
    Set wsSummary = ThisWorkbook.Sheets("Summary")
    On Error GoTo 0   
    ' If the "Summary" sheet exists, delete it
    If Not wsSummary Is Nothing Then
        Application.DisplayAlerts = False
        wsSummary.Delete
        Application.DisplayAlerts = True
    End If   
    ' Create a new "Summary" sheet
    Set wsSummary = ThisWorkbook.Sheets.Add
    wsSummary.Name = "Summary"   
    ' Find the last row of data
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row   
    ' Loop through the data and fill the dictionaries
    For i = 2 To lastRow ' Start at row 2 to ignore the header
        monthDate = Format(wsSource.Cells(i, 1).Value, "yyyy-mm") ' Extract year and month
        product = wsSource.Cells(i, 2).Value
        region = wsSource.Cells(i, 3).Value
        sales = wsSource.Cells(i, 4).Value       
        ' Update the product dictionary
        If Not dictProducts.Exists(product) Then
            dictProducts.Add product, 0
        End If
        dictProducts(product) = dictProducts(product) + sales       
        ' Update the region dictionary
        If Not dictRegions.Exists(region) Then
            dictRegions.Add region, 0
        End If
        dictRegions(region) = dictRegions(region) + sales       
        ' Update the month dictionary
        If Not dictMonths.Exists(monthDate) Then
            dictMonths.Add monthDate, 0
        End If
        dictMonths(monthDate) = dictMonths(monthDate) + sales
    Next i   
    ' Add headers to the "Summary" sheet
    wsSummary.Cells(1, 1).Value = "Criteria"
    wsSummary.Cells(1, 2).Value = "Total Sales"   
    ' Fill in the product results
    wsSummary.Cells(2, 1).Value = "By Product"
    wsSummary.Cells(3, 1).Value = "Product"
    wsSummary.Cells(3, 2).Value = "Total Sales"
    i = 4
    For Each Key In dictProducts.Keys
        wsSummary.Cells(i, 1).Value = Key
        wsSummary.Cells(i, 2).Value = dictProducts(Key)
        i = i + 1
    Next Key   
    ' Add a blank row between sections
    i = i + 1   
    ' Fill in the region results
    wsSummary.Cells(i, 1).Value = "By Region"
    wsSummary.Cells(i + 1, 1).Value = "Region"
    wsSummary.Cells(i + 1, 2).Value = "Total Sales"
    i = i + 2
    For Each Key In dictRegions.Keys
        wsSummary.Cells(i, 1).Value = Key
        wsSummary.Cells(i, 2).Value = dictRegions(Key)
        i = i + 1
    Next Key   
    ' Add a blank row between sections
    i = i + 1   
    ' Fill in the month results
    wsSummary.Cells(i, 1).Value = "By Month"
    wsSummary.Cells(i + 1, 1).Value = "Month"
    wsSummary.Cells(i + 1, 2).Value = "Total Sales"
    i = i + 2
    For Each Key In dictMonths.Keys
        wsSummary.Cells(i, 1).Value = Key
        wsSummary.Cells(i, 2).Value = dictMonths(Key)
        i = i + 1
    Next Key   
    ' Formatting
    wsSummary.Columns("A:B").AutoFit
    wsSummary.Cells(1, 1).Font.Bold = True
    wsSummary.Cells(1, 2).Font.Bold = True  
    MsgBox "Summary complete!", vbInformation
End Sub

Code Explanation

1. Variable Declarations
   • wsSource: Reference to the worksheet containing the raw data (« Data »).
   • wsSummary: Reference to the worksheet where the summary will be created.
   • dictProducts, dictRegions, dictMonths: Dictionaries used to store and accumulate sales by product, region, and month.
   • lastRow: To find the last row of data in the source sheet.
2. Initializing Dictionaries
   • We use dictionaries to store accumulated sales data. This makes it easy to add and update items based on the keys (product, region, and month).
3. Loop Through Data
   • The loop For i = 2 To lastRow processes each row in the « Data » sheet, starting from the second row to skip the header.
   • For each row, we extract the date, product, region, and sales, and update the corresponding dictionaries.
4. Creating the Summary
   • After accumulating the data in the dictionaries, we generate a new sheet called « Summary » and insert the totals by product, region, and month.
5. Formatting
   • The AutoFit method is used to automatically adjust column widths.
   • The headers are formatted in bold.

How to Use the Code

To use this VBA code:

1. Open your Excel file.
2. Press Alt + F11 to open the VBA editor.
3. Go to Insert > Module and paste the code.
4. Press F5 or run the macro from the Excel interface under the Developer > Macros menu.

This will generate a complete summary of sales by product, region, and month on a new sheet. You can adjust this code according to your specific needs.