Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Relative Reference help | Excel Discussion (Misc queries) | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
relative reference | Excel Discussion (Misc queries) | |||
relative reference | Excel Programming | |||
relative reference | Excel Programming |