relative reference pivot table macro

[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

 

[Trevor Williams]

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

 

[Joel]

Try this

Sub CreateTbl()
Set Source = Sheets("AP Data").Range("A1")
Region = Source.CurrentRegion _
.Address(external:=True, ReferenceStyle:=xlR1C1)

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=Region).CreatePivotTable _
TableDestination:="", _
TableName:="PivotTable8", _
DefaultVersion:=xlPivotTableVersion10

End Sub