MACRO TO CREATE A PIVOT TABLE

[O177812]

Hi I am trying to create a macro that will create a pivot table for data that changes daily.

Currently I am using the below:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"BY SKU!R1C1:R79C21", Version:=7).CreatePivotTable TableDestination:= _
"Sheet1!R3C1", TableName:="PivotTable5", DefaultVersion:=7

However, sometimes there is more or less than 79 rows of data so either I am getting "blanks" or losing data.
How can I fix the above so that it selects all based on the data on the sheet?

Any assistance is greatly appreciated.

 

[Domenic]

Try the following . . .

    Dim sourceRange As Range
    Set sourceRange = Worksheets("BY SKU").Range("A1").CurrentRegion

    Dim PTCache As PivotCache
    Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=sourceRange)
    
    Dim PT As PivotTable
    Set PT = ActiveWorkbook.PivotTables.Add(PivotCache:=PTCache, TableDestination:=Range("A3"), TableName:="PivotTable5")

Hope this helps!