arraysexcelvbaconsolidation

How to consolidate data that is being added to every day

I am trying to take data from datafile.xlsx (A1-C7) and consolidate it using the consolidate command into A1 in the same workbook, different sheet

Dim iwb As Workbook: Set iwb = Workbooks.Open("datafile.xlsx")
Dim insp As Worksheet: Set insp = iwb.Sheets(1)
Dim tot As Worksheet: Set tot = iwb.Sheets(2)

tot.Range("A1").consolidate _
    Sources:=Array("Inspector Data!R1C1:R7C3"), _
    Function:=xlSum, _
    TopRow:=True, _
    LeftColumn:=True

However, this data will grow with a new row being added each day. How do I tell the consolidate command or the array to reselect all of the data? Similar to how range.currentregion does.

Solution 
  • Dim iwb As Workbook: Set iwb = Workbooks.Open("datafile.xlsx")
Dim insp As Worksheet: Set insp = iwb.Sheets(1)
Dim tot As Worksheet: Set tot = iwb.Sheets(2)

Dim lastrow As Long
    
With insp
    lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With

tot.Range("A1").consolidate _
    Sources:=Array("Inspector Data!R1C1:R" & lastrow & "C3"), _
    Function:=xlSum, _
    TopRow:=True, _
    LeftColumn:=True