Consolidate - Relative Reference
 

Hi all,

A while ago I asked about making a Macro portable (i.e. relative
references) - someone kindly responded but at the time it was beyond me to
implement. I have learned since then and have now managed to implement -
basically, open, manipulate, close and save workbooks is working fine.
However, in my 'summary' workbook I consolidate data from 4 other workbooks
and I'm struggling to edit the code to make this portable. My current code
follows and although it doesn't produce any errors, it does not consolidate
the data (if I quote the full path it works fine). The summary workbook is
always in the parent directory above Q1, Q2, Q3 and Q4:

Range("A7").Select
Selection.Consolidate Sources:=Array( _
ActiveWorkbook.Path & "'\Q1\[SummaryQ1.xls]01'!R7C1:R205C20", _
ActiveWorkbook.Path & "'\Q2\[SummaryQ2.xls]01'!R7C1:R205C20", _
ActiveWorkbook.Path & "'\Q3\[SummaryQ3.xls]01'!R7C1:R205C20", _
ActiveWorkbook.Path & "'\Q4\[SummaryQ4.xls]01'!R7C1:R205C20"), _
Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2

Am I making a basic mistake or is it not quite as simple as I'm trying to
make it?

Many thanks.

Paul.

Consolidate - Relative Reference
 

You have your first single quote in the wrong place

ActiveWorkbook.Path & "'\Q1\[SummaryQ1.xls]01'!R7C1:R205C20",

should be

"'" & ActiveWorkbook.Path & "\Q1\[SummaryQ1.xls]01'!R7C1:R205C20",

otherwise it appears in the middle of your path.

--
Regards,
Tom Ogilvy

"Skip" wrote in message
...
Hi all,

A while ago I asked about making a Macro portable (i.e. relative
references) - someone kindly responded but at the time it was beyond me to
implement. I have learned since then and have now managed to implement -
basically, open, manipulate, close and save workbooks is working fine.
However, in my 'summary' workbook I consolidate data from 4 other
workbooks
and I'm struggling to edit the code to make this portable. My current code
follows and although it doesn't produce any errors, it does not
consolidate
the data (if I quote the full path it works fine). The summary workbook is
always in the parent directory above Q1, Q2, Q3 and Q4:

Range("A7").Select
Selection.Consolidate Sources:=Array( _
ActiveWorkbook.Path & "'\Q1\[SummaryQ1.xls]01'!R7C1:R205C20", _
ActiveWorkbook.Path & "'\Q2\[SummaryQ2.xls]01'!R7C1:R205C20", _
ActiveWorkbook.Path & "'\Q3\[SummaryQ3.xls]01'!R7C1:R205C20", _
ActiveWorkbook.Path & "'\Q4\[SummaryQ4.xls]01'!R7C1:R205C20"), _
Function:=xlSum, TopRow:=False, LeftColumn:=True,
CreateLinks:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2

Am I making a basic mistake or is it not quite as simple as I'm trying to
make it?

Many thanks.

Paul.

Consolidate - Relative Reference
 

Many thanks Tom,

Now works as I'd hoped.

Paul.

"Tom Ogilvy" wrote in message
...
You have your first single quote in the wrong place

ActiveWorkbook.Path & "'\Q1\[SummaryQ1.xls]01'!R7C1:R205C20",

should be

"'" & ActiveWorkbook.Path & "\Q1\[SummaryQ1.xls]01'!R7C1:R205C20",

otherwise it appears in the middle of your path.

--
Regards,
Tom Ogilvy

"Skip" wrote in message
...
Hi all,

A while ago I asked about making a Macro portable (i.e. relative
references) - someone kindly responded but at the time it was beyond me
to
implement. I have learned since then and have now managed to implement -
basically, open, manipulate, close and save workbooks is working fine.
However, in my 'summary' workbook I consolidate data from 4 other
workbooks
and I'm struggling to edit the code to make this portable. My current
code
follows and although it doesn't produce any errors, it does not
consolidate
the data (if I quote the full path it works fine). The summary workbook
is
always in the parent directory above Q1, Q2, Q3 and Q4:

Range("A7").Select
Selection.Consolidate Sources:=Array( _
ActiveWorkbook.Path & "'\Q1\[SummaryQ1.xls]01'!R7C1:R205C20", _
ActiveWorkbook.Path & "'\Q2\[SummaryQ2.xls]01'!R7C1:R205C20", _
ActiveWorkbook.Path & "'\Q3\[SummaryQ3.xls]01'!R7C1:R205C20", _
ActiveWorkbook.Path & "'\Q4\[SummaryQ4.xls]01'!R7C1:R205C20"), _
Function:=xlSum, TopRow:=False, LeftColumn:=True,
CreateLinks:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2

Am I making a basic mistake or is it not quite as simple as I'm trying
to
make it?

Many thanks.

Paul.