Would you be able to help me run a consolidation using a string variable for the "sources" argument? The consolidation runs fine using both specific ranges or named ranges as shown in versions 1 and 2 below. As soon as I substitute a variable for the sources as in version 3, I receive an error message that it cannot open the source file. This is true even with the variable set to the exact same string as in either 1 or 2. I've also tested it running the consolidation from another workbook.
Thanks very much for your guidance.
1 and 2 below both run correctly:
Code:
(1) ThisWorkbook.Sheets(1).Range("B19").Consolidate Sources:=Array("Sheet1!R2C2:R8C6", "Sheet1!R10C2:R16C6"), Function:=xlSum
(2) ThisWorkbook.Sheets(1).Range("B19").Consolidate Sources:=Array("'C:\testconsolidate.xls'!group1", "'C:\testconsolidate.xls'!group2"), Function:=xlSum
but the consolidation doesn't run with a variable for sources, for example:
Code:
strNmrng = "'C:\testconsolidate.xls'!group1" & ", " & "'C:\testconsolidate.xls'!group2"
(3) ThisWorkbook.Sheets(1).Range("B19").Consolidate Sources:=strNmrng, Function:=xlSum
#3 returns error that cannot open source file testconsolidate.xls
string variable for consolidation
