qitjch
07-15-2016, 11:01 AM
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 (https://drive.google.com/open?id=0B7oMo2XZIUFkRTZCdk1hUHI0WHc)" 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?
I copied the 2 worksheets involved into a new workbook, "consolidation_wb.xlsx (https://drive.google.com/open?id=0B7oMo2XZIUFkRTZCdk1hUHI0WHc)" 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?