Automate data aggregation processes, Excel VBA

Here is an example of VBA code that automates the process of aggregating data from multiple sheets into a consolidated summary sheet. The code collects data from a specific column in each sheet and then aggregates it on a separate « Consolidated » sheet.

Scenario:

We have several worksheets in an Excel workbook, and we want to aggregate data from a specific column in each sheet into one summary sheet. For example, each sheet contains sales data, and we want to summarize this information on a single sheet.

Steps in the example:

1. Each sheet has a column with numerical data that we want to sum.
2. We’ll create a « Consolidated » sheet that will hold the summarized data.
3. The VBA code will loop through each sheet, extract the values, and copy them into the « Consolidated » sheet.

VBA Code Example:

Sub AggregateData()
    Dim ws As Worksheet
    Dim wsConsolidated As Worksheet
    Dim row As Long
    Dim total As Double
    Dim lastRow As Long
    Dim cell As Range
    Dim sourceCol As Long
    Dim consolidatedCol As Long
    ' Create the "Consolidated" sheet if it doesn't exist
    On Error Resume Next
    Set wsConsolidated = ThisWorkbook.Sheets("Consolidated")
    On Error GoTo 0
    If wsConsolidated Is Nothing Then
        Set wsConsolidated = ThisWorkbook.Sheets.Add
        wsConsolidated.Name = "Consolidated"
    End If
    ' Initialize columns (e.g., Column A for sheet names, Column B for sums)
    wsConsolidated.Cells.Clear ' Clear old data
    wsConsolidated.Cells(1, 1).Value = "Sheet Name"
    wsConsolidated.Cells(1, 2).Value = "Sum of Values"
    sourceCol = 1 ' Assuming data is in Column A of other sheets
    consolidatedCol = 2 ' We'll put results in Column B of the "Consolidated" sheet
    row = 2 ' Start at row 2 in the "Consolidated" sheet
    ' Loop through each sheet in the workbook
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "Consolidated" Then ' Skip the "Consolidated" sheet
            ' Find the last row with data in the source column
            lastRow = ws.Cells(ws.Rows.Count, sourceCol).End(xlUp).Row
            total = 0 ' Reset the total before summing
            ' Sum the values in the source column of the current sheet
            For Each cell In ws.Range(ws.Cells(1, sourceCol), ws.Cells(lastRow, sourceCol))
                If IsNumeric(cell.Value) Then
                    total = total + cell.Value
                End If
            Next cell       
            ' Copy results to the "Consolidated" sheet
            wsConsolidated.Cells(row, 1).Value = ws.Name ' Sheet name
            wsConsolidated.Cells(row, 2).Value = total ' Sum of values
            row = row + 1 ' Move to the next row for the next sheet
        End If
    Next ws   
    MsgBox "Data aggregation is complete!"
End Sub

Explanation of the code:

1. Create the « Consolidated » sheet:
   • The code first checks if the « Consolidated » sheet already exists. If it doesn’t, it creates it. It then prepares the columns where the sheet names and summed values will be placed: Column A for the sheet names and Column B for the summed values.
2. Initialize variables:
   • sourceCol refers to the column in each sheet from which data will be aggregated (in this case, column A).
   • consolidatedCol is the column in the « Consolidated » sheet where the results will be written (in this case, column B).
   • row is the starting row index in the « Consolidated » sheet where results will be written.
3. Loop through all sheets:
   • The code loops through every sheet in the workbook. If the sheet’s name is not « Consolidated, » it moves to the next step.
4. Sum the values:
   • For each sheet, the code identifies the last row of data in the source column (Column A) and then loops through each cell in that range, summing the values that are numeric.
5. Write results to the « Consolidated » sheet:
   • The name of the sheet is written in Column A of the « Consolidated » sheet, and the total sum is written in Column B in the corresponding row.
6. Completion Message:
   • Once all sheets have been processed, a message box appears to notify the user that the aggregation is complete.

How to use this code:

1. Open your Excel file.
2. Press Alt + F11 to open the VBA editor.
3. In the editor, click Insert > Module to create a new module.
4. Paste the code into the module.
5. Close the VBA editor and return to Excel.
6. Run the macro by pressing Alt + F8, selecting AggregateData, and clicking Run.

Result:

The code will create a new sheet called « Consolidated » with the names of each sheet and the aggregated sums of the data in each sheet’s column. This provides a quick overview of the aggregated values from multiple sheets in one place.