variable data range for pivot table in macro

[jess89]

Hi

I have a macro, that contains the creation of a pivot table. Unfortunately, the data for the pivot table is not always the same size. How can I adjust my macro, that the data for the pivot may be variable?

Here is the part of my macro, that creates the pivot table:

Sheets("Data").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R521C36", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="pivottemp!R2C1", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
Sheets("pivottemp").Select

-> How can I adjust the "SourceData:=_" part?


Thanks for your help

 

[Gregorys05]

Try somthing like this

Set PTCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:="'" & ActiveSheet.Name & "'!" & Range("A3").CurrentRegion.Address(True, True, xlR1C1))

 

[jess89]

Perfect, it works.

Thanks