Pivot Table

[Dummy Excel]

Hi All,
Im creating a macro to perform a pivot table. My code is below, how do I change the row details so that its dynamic as the number of rows will change each week when the macro is running?

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "PO!R1C1:R16547C25", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("Sheet1").Select

thanks
Sam

 

[Dummy Excel]

anyone??

 

[Marcelo Branco]

Hi,

Maybe

Assuming A1 is the upper-left cell of your data-range you could use the CurrentRegion property like

Sub CreatePVT()
 
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Sheets("PO").Range("A1").CurrentRegion, Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:=Sheets("Sheet1").Range("A3"), TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("Sheet1").Select
End Sub

M.