Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
relative reference pivot table macro
Hi,
Every month, I create the same pivot table from a data sheet and every month, the number of row is different. I want to record the macro to do this. When I record the macro and i select my source range, using shift+end+ right and then shift+end+down, the macro records the cell range as an absolute value. If i use the relative reference button when i start the recording, it still records it using absolute refs The below is a recording with the relative reference button clicked ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'AP Data'!R1C1:R10077C20").CreatePivotTable TableDestination:="", TableName _ :="PivotTable8", DefaultVersion:=xlPivotTableVersion10 The only way I can think to do it is highlighting all columns but that adds a blank value to my pivot table when its complete. I know i can then deselect it but thats beside the point. Any ideas? I did try to declare a variable as a changeable range of cells and using that in the pivot table source cache but couldnt get it working. I am a complete novice at VBA I have to add. Thanks Rob -- Rob Gaffney |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
relative reference pivot table macro
Hi Rob
Try naming your data as a dynamic range and then referencing the range name in your pivot data. e.g. if you name your data range as myDataRange change your code to ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "=myDataRange").CreatePivotTable TableDestination:="", TableName _ :="PivotTable8", DefaultVersion:=xlPivotTableVersion10 To name a dynamic range see Debra Dalgleish's Contextures here... http://www.contextures.com/xlNames01.html#Dynamic HTH Trevor Williams "Gaffnr" wrote: Hi, Every month, I create the same pivot table from a data sheet and every month, the number of row is different. I want to record the macro to do this. When I record the macro and i select my source range, using shift+end+ right and then shift+end+down, the macro records the cell range as an absolute value. If i use the relative reference button when i start the recording, it still records it using absolute refs The below is a recording with the relative reference button clicked ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'AP Data'!R1C1:R10077C20").CreatePivotTable TableDestination:="", TableName _ :="PivotTable8", DefaultVersion:=xlPivotTableVersion10 The only way I can think to do it is highlighting all columns but that adds a blank value to my pivot table when its complete. I know i can then deselect it but thats beside the point. Any ideas? I did try to declare a variable as a changeable range of cells and using that in the pivot table source cache but couldnt get it working. I am a complete novice at VBA I have to add. Thanks Rob -- Rob Gaffney |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
relative reference pivot table macro
Hi Rob
Try naming your data as a dynamic range and then referencing the range name in your pivot data. e.g. if you name your data range as myDataRange change your code to ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "=myDataRange").CreatePivotTable TableDestination:="", TableName _ :="PivotTable8", DefaultVersion:=xlPivotTableVersion10 To name a dynamic range see Debra Dalgleish's Contextures here... http://www.contextures.com/xlNames01.html#Dynamic HTH Trevor Williams "Gaffnr" wrote: Hi, Every month, I create the same pivot table from a data sheet and every month, the number of row is different. I want to record the macro to do this. When I record the macro and i select my source range, using shift+end+ right and then shift+end+down, the macro records the cell range as an absolute value. If i use the relative reference button when i start the recording, it still records it using absolute refs The below is a recording with the relative reference button clicked ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'AP Data'!R1C1:R10077C20").CreatePivotTable TableDestination:="", TableName _ :="PivotTable8", DefaultVersion:=xlPivotTableVersion10 The only way I can think to do it is highlighting all columns but that adds a blank value to my pivot table when its complete. I know i can then deselect it but thats beside the point. Any ideas? I did try to declare a variable as a changeable range of cells and using that in the pivot table source cache but couldnt get it working. I am a complete novice at VBA I have to add. Thanks Rob -- Rob Gaffney |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
relative reference pivot table macro
Try this
Sub CreateTbl() Set Source = Sheets("AP Data").Range("A1") Region = Source.CurrentRegion _ .Address(external:=True, ReferenceStyle:=xlR1C1) ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _ SourceData:=Region).CreatePivotTable _ TableDestination:="", _ TableName:="PivotTable8", _ DefaultVersion:=xlPivotTableVersion10 End Sub "Gaffnr" wrote: Hi, Every month, I create the same pivot table from a data sheet and every month, the number of row is different. I want to record the macro to do this. When I record the macro and i select my source range, using shift+end+ right and then shift+end+down, the macro records the cell range as an absolute value. If i use the relative reference button when i start the recording, it still records it using absolute refs The below is a recording with the relative reference button clicked ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'AP Data'!R1C1:R10077C20").CreatePivotTable TableDestination:="", TableName _ :="PivotTable8", DefaultVersion:=xlPivotTableVersion10 The only way I can think to do it is highlighting all columns but that adds a blank value to my pivot table when its complete. I know i can then deselect it but thats beside the point. Any ideas? I did try to declare a variable as a changeable range of cells and using that in the pivot table source cache but couldnt get it working. I am a complete novice at VBA I have to add. Thanks Rob -- Rob Gaffney |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro- Relative reference | Excel Programming | |||
Relative reference? in macro | Excel Worksheet Functions | |||
Relative reference to a table across multiple worksheets | Excel Programming | |||
Copy data from pivot table using relative reference | Excel Discussion (Misc queries) | |||
Relative Reference in a Macro | Excel Programming |