Consolidating multiple files using a row column range
set rng = Range("$Q$112:$AL$4127")
? rng.address(true,true,xlr1c1)
R112C17:R4127C38
looks good to me.
Regards,
Tom Ogilvy
"Jim" wrote in message
...
Thanks Tom,
I did the following:
Selection.Consolidate Sources:=Array( _
"'C:\t\[a.xls]Sheet1'!R112C17:R4127C38" _
, "'C:\t\[b.xls]Sheet1'!R112C17:R4127C38" _
, "'C:\t\[c.xls]Sheet1'!R112C17:R4127C38" _
, "'C:\t\[d.xls]Sheet1'!R112C17:R4127C38" _
, "'C:\t\[e.xls]Sheet1'!R112C17:R4127C38" _
, "'C:\t\[f.xls]Sheet1'!R112C17:R4127C38"), _
Function:=xlSum, TopRow:=False, _
LeftColumn:=True, CreateLinks:=False
This did pass syntax, but gave me inaccurate results.
I converted !$Q$112:$AL$4127 to R112C17:R4127C38
Do you think that was right???
TIA,
Jim
-----Original Message-----
Think you need to replace your references with R1C1
notation. This is what
I recorded as an example:
Selection.Consolidate Sources:=Array( _
"'C:\Data\[Alex1.xls]Sheet1'!R3C3:R12C4" _
, "'C:\Data\[Alex2.xls]Sheet1'!R3C3:R12C4"), _
Function:=xlSum, TopRow:=False, _
LeftColumn:=True, CreateLinks:=False
Regards,
Tom Ogilvy
"Jim" wrote in message
...
Hi, trying to get the correct syntax for the following
file consolidation.
For files b,c,d,e,f I was using name1
I want to convert them to a specific range of
xls'Sheet1!
$Q$112:$AL$4127"
When I tried for file a, I got a macro error -
consolidation reference is not valid.
Selection.Consolidate Sources:=Array
("'C:\t\a.xls'Sheet1!
$Q$112:$AL$4127", _
"'C:\t\b.xls'!name1", "'C:\t\c.xls'!
name1", "'C:\t\d.xls'!name1", _
"'C:\t\e.xls'!name1", "'C:\t\f.xls'!name1"),
Function:=xlSum, TopRow:=False _
, LeftColumn:=False, CreateLinks:=False
Thanks,
.
|