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