Is it possible to automate a repetitive Excel task using VBA

Hi @Nettleton

Thank you for reaching out to Microsoft Q&A.

Based on the information you described, I understand that you are looking to automate a workflow that includes Validation > Transformation > Consolidation of data from multiple worksheets into a single summary sheet. It is crucial that the solution handles dynamic data ranges and processes missing values safely.

I have tested a solution on a small dataset within the Excel for MacOS environment, and it successfully achieves the desired outcome. I recommend running the following VBA code on a copy of your file first to ensure the results meet your expectations.

Here is the code script:

Sub ConsolidateSheets()
   Dim ws As Worksheet
   Dim summaryWS As Worksheet
   Dim lastRow As Long
   Dim lastCol As Long
   Dim summaryRow As Long
   Dim dataRange As Range
   Dim cell As Range
   Application.ScreenUpdating = False
   Application.DisplayAlerts = False
   ' Create or clear the summary sheet
   On Error Resume Next
   Set summaryWS = ThisWorkbook.Worksheets("Summary")
   On Error GoTo 0
   If summaryWS Is Nothing Then
       Set summaryWS = ThisWorkbook.Worksheets.Add
       summaryWS.Name = "Summary"
   Else
       summaryWS.Cells.Clear
   End If
   summaryRow = 1
   ' Loop through all worksheets except "Summary"
   For Each ws In ThisWorkbook.Worksheets
       If ws.Name <> summaryWS.Name Then
           ' Find last row & column with data
           lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
           lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
           If lastRow > 1 And lastCol > 0 Then
               Set dataRange = ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, lastCol))
               ' Copy headers once
               If summaryRow = 1 Then
                   ws.Range(ws.Cells(1, 1), ws.Cells(1, lastCol)).Copy _
                       Destination:=summaryWS.Cells(summaryRow, 1)
                   summaryRow = summaryRow + 1
               End If
               ' Loop through data rows
               For Each cell In dataRange.Rows
                   Dim rValues As Variant
                   Dim i As Long
                   rValues = cell.Value
                   ' Validate or clean missing values here
                   For i = LBound(rValues, 2) To UBound(rValues, 2)
                       If IsEmpty(rValues(1, i)) Then
                           rValues(1, i) = "N/A"
                       End If
                   Next i
                   ' Add to summary
                   summaryWS.Cells(summaryRow, 1).Resize(1, UBound(rValues, 2)).Value = rValues
                   summaryRow = summaryRow + 1
               Next cell
           End If
       End If
   Next ws
   Application.ScreenUpdating = True
   Application.DisplayAlerts = True
   MsgBox "Consolidation complete!"
End Sub

After adding the VBA code, you need to add a button to your worksheet to run the macro easily. Please follow these steps:

1. First, ensure your file is saved as an Excel Macro-Enabled Workbook (.xlsm) and that macros are enabled in your security settings.
2. Enable the Developer Tab> Go to Excel (in the top menu bar) > Preferences > Select Ribbon & Toolbar > In the right-hand list, check the box next to Developer to add it to your ribbon.
3. Insert the Button > Go to the Developer tab > Click Button > Draw the button anywhere on your sheet > In the pop-up dialog, select ConsolidateSheets and click OK.

Now, whenever you have new data, you just need to click this button to activate the macro and update your summary.

I hope this helps! Please let me know if you need any further adjustments.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Yes, this is a common and well-suited use case for Excel VBA. A VBA macro can loop through multiple worksheets, dynamically detect used ranges, validate values, apply transformations, and consolidate results into a summary sheet, all triggered by a button on the worksheet or ribbon.

Dynamic ranges are typically handled using constructs such as UsedRange, finding the last row or column with End(xlUp) or End(xlToLeft), or by iterating until blank cells are encountered. Missing or invalid values can be handled safely with conditional checks like IsEmpty, IsNumeric, or error trapping using On Error Resume Next with immediate error handling reset.

A typical flow is: clear or prepare the summary sheet, loop through each source worksheet, read values into variables or arrays, validate and transform them in memory for performance, then write the results to the next available row in the summary sheet. Using arrays instead of cell-by-cell writes greatly improves speed and robustness.

To allow one-click execution, you can assign the macro to a Form Control or ActiveX button

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin