Automate data synthesis in Excel with VBA

Automating data synthesis in Excel with VBA can be very useful for saving time and reducing human errors. Below is a detailed example of VBA code that can be used to automate this process. The goal of this code is to consolidate data from multiple sheets into a single summary table.

Scenario

Imagine that we have several worksheets in an Excel workbook, each containing similar data (for example, sales data from different regions), and we want to automate the process of consolidating this data into a « Summary » sheet.

Step 1: Preparing the Data

Assume that each sheet contains sales data with the following columns:

• Product Name
• Quantity Sold
• Revenue

We want to compile this information into a summary sheet.

Step 2: Structure of the Code

Here’s an example of VBA code that loops through each worksheet, retrieves the data, and copies it to a summary sheet.

VBA Code for Automating Data Synthesis

1. Access the VBA editor:
   • Press Alt + F11 to open the VBA editor.
   • Click on Insert and then Module to insert a new module.
2. Write the VBA code:

Sub AutomateSynthesis()
    ' Declare variables
    Dim wsSummary As Worksheet
    Dim ws As Worksheet
    Dim i As Long, lastRow As Long, summaryRow As Long
    Dim dataRange As Range
    Dim cell As Range   
    ' Create or activate the Summary sheet
    On Error Resume Next
    Set wsSummary = ThisWorkbook.Sheets("Summary")
    On Error GoTo 0   
    If wsSummary Is Nothing Then
        ' If the "Summary" sheet does not exist, create it
        Set wsSummary = ThisWorkbook.Sheets.Add
        wsSummary.Name = "Summary"
    End If   
    ' Clear old data in the Summary sheet
    wsSummary.Cells.Clear
    ' Add headers for the summary
    wsSummary.Cells(1, 1).Value = "Product Name"
    wsSummary.Cells(1, 2).Value = "Total Quantity"
    wsSummary.Cells(1, 3).Value = "Total Revenue"   
    summaryRow = 2  ' Row to start pasting data   
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Sheets
        ' Skip the Summary sheet itself
        If ws.Name <> "Summary" Then
            ' Find the last row of data in the current sheet
            lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
            ' Set the data range to copy (from A2 to the last row in columns A-C)
            Set dataRange = ws.Range("A2:C" & lastRow)           
            ' Copy data to the Summary sheet
            For Each cell In dataRange.Rows
                ' Copy each row of data to the Summary sheet
                wsSummary.Cells(summaryRow, 1).Value = cell.Cells(1, 1).Value  ' Product Name
                wsSummary.Cells(summaryRow, 2).Value = wsSummary.Cells(summaryRow, 2).Value + cell.Cells(1, 2).Value ' Total Quantity
                wsSummary.Cells(summaryRow, 3).Value = wsSummary.Cells(summaryRow, 3).Value + cell.Cells(1, 3).Value ' Total Revenue      
                ' Move to the next row in the Summary sheet
                summaryRow = summaryRow + 1
            Next cell
        End If
    Next ws
    ' Message box to indicate the synthesis is complete
    MsgBox "Data synthesis has been completed successfully!", vbInformation
End Sub

Code Explanation

1. Variable declarations:
   • wsSummary: This is the variable for the « Summary » sheet.
   • ws: This represents each worksheet.
   • i, lastRow, summaryRow: These variables manage the row positions.
   • dataRange: This holds the range of data to copy.
   • cell: This variable is used to loop through each row of data.
2. Create or activate the summary sheet:
   • The code first checks if a sheet named « Summary » already exists. If it doesn’t, it creates one.
3. Clear old data:
   • If the summary sheet already exists, the previous data is cleared using Cells.Clear.
4. Add headers:
   • Headers like « Product Name, » « Total Quantity, » and « Total Revenue » are added in row 1 of the summary sheet.
5. Loop through each sheet:
   • The code loops through each worksheet in the workbook, skipping the « Summary » sheet.
   • For each sheet, it finds the last row of data (lastRow) and defines the data range to copy (dataRange).
6. Copy the data:
   • For each row in the data range, the code copies the product name, quantity, and revenue into the summary sheet.
   • It accumulates the totals in the « Total Quantity » and « Total Revenue » columns.
7. Confirmation message:
   • Once the automation is complete, a message box is displayed to inform the user that the synthesis has been completed successfully.

Step 3: Running the Code

To execute the code:

1. In the VBA editor, press F5 or go to « Run » > « Run Sub/UserForm. »
2. The code will loop through all sheets, collect the data, and paste it into the « Summary » sheet.

Step 4: Customization

• You can adapt this code to include additional types of summaries, such as averages, specific filters, or even generate charts.
• The code can be enhanced to handle specific errors, such as empty cells or incorrect formats.

This code provides a simple framework for automating data consolidation in Excel, and you can modify it to suit your specific needs!