G
Gaffnr
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:=xlDatabase, 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
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:=xlDatabase, 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