Pivottable References

[T De Villiers]

Hi, I have a pivot table macro on a dynamic range of data with varying
number of rows and columns.
My code below isnt quite working, pretty sure its to do with:
SourceData:= "Original!cells(x,1),cells(1,y)")

Many Thks

Sub test()

x = Range("A50000").End(xlUp).Row
y = Range("IV1").End(xlToLeft).Column


Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:
_
"Original!cells(x,1),cells(1,y)").CreatePivotTabl
TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3
1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Cat4"
_
ColumnFields:="Account"

ActiveSheet.PivotTables("PivotTable1").PivotFields("Amount").Orientatio
= _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveSheet.Name = "Check"
End Su

 

[Die_Another_Day]

Change "Original!cells(x,1),cells(1,y)").
to this
"Original!" & cells(x,1).Address & "," cells(1,y).Address).

HTH

Die_Another_Day