Issue consolidating data to another worksheet

[qitjch]

I am trying to consolidate ~8000 rows of transactions onto another sheet. I am able to do it manually and it works. When I try with VBA, the consolidation just results in nothing showing up on the 2nd worksheet.

I copied the 2 worksheets involved into a new workbook, "consolidation_wb.xlsx" that I will link to this post. Within the workbook, I have 2 worksheets (1) IND-External and (2) Concat. I want to consolidate the data from IND-External to Concat, using column A as the label (left column) and in the process summing up the data in the other columns. As I stated, this works manually and results in 1896 rows. When I use VBA, its results in nothing being consolidated onto the Concat tab.

'calculate new last row once all transactions have been imported
   lastrow2 = Worksheets("IND-External").Cells(Rows.Count, 2).End(xlUp).Row

'set data range to variable
   Set ExternalData = Worksheets("IND-External").Range("A3:K" & lastrow2)

'consolidate data
   Worksheets("Concat").Range("A4").Select
        Selection.Consolidate Sources:= _
            "'\\FILESERVER\Manufacturing\Finance\Daily Reporting\Volume\[consolidation_wb.xlsx]IND-External'!" & _
                ExternalData.Address(ReferenceStyle:=xlR1C1) , _
                Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False

I also used the macro recorder and tried to replicate it, but no luck. When I use the immediate window to look at the Source string above, it shows up correctly and the ExternalData variable also contains the correct range.

Any ideas?

[mdmackillop]

Sub Test()
Dim d As New Collection
Dim arr()
Dim r As Range
Application.ScreenUpdating = False
On Error Resume Next
For Each cel In Range("Data")
d.Add cel, cel
Next
On Error GoTo 0
ReDim arr(d.Count - 1)
For Each it In d
arr(i) = it
i = i + 1
Next
Set r = Sheets("Concat").Cells(4, 1).Resize(d.Count)
r = Application.Transpose(arr)
r.Offset(, 6).FormulaR1C1 = "=IF(ISNUMBER(RIGHT(RC[-6],6)*1),COUNTIF('IND-External'!C[-6],Concat!RC[-6])*RIGHT(RC[-6],6),"""")"
r.Offset(, 8).FormulaR1C1 = "=SUMIF('IND-External'!C1,Concat!RC1,'IND-External'!C)"
r.Offset(, 9).FormulaR1C1 = "=SUMIF('IND-External'!C1,Concat!RC1,'IND-External'!C)"
r.Offset(, 10).FormulaR1C1 = "=SUMIF('IND-External'!C1,Concat!RC1,'IND-External'!C)"
'Remove formulae
r.Resize(, 11).Value = r.Resize(, 11).Value
Application.ScreenUpdating = False
End Sub

[qitjch]

So far this is working wonderfully. Thank you for the assist!

[mdmackillop]

Happy to help. Note that Column G depends upon the last part of column A being 6 characters. If this may not always be the case, then something more robust is required to check the last section of the data.