Dynamic Table Array for Pivot Table in VBA

[QnA]

Hello experts,

a little new at this but i have done all the research i can and cannot pinpoint the solution.

i would like to build a macro to create a pivot table on a data set that grows in length each week. The part i am having trouble with is having the pivot table include the entire data set each time. The SourceData section stays static at "Sheet1!R1C1:R26958" instead of including the new table size.

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R26958", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14

thoughts? Thank you in advance!

[mana]

you want to create new pivot table every week?
or only updating?

[p45cal]

As mana says, perhaps you don't need to re-create the pivot table from scratch - just change the data it's looking at.
You might get away with just:

Sub blah()
With Sheets("Sheet2").Range("A3").PivotTable
  .ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheets("Sheet1").Range("A1").CurrentRegion, Version:=xlPivotTableVersion14)
  .PivotCache.Refresh
End With
End Sub

However, is it actually an Excel Table that gets updated every time - does it keep its name? If so then you need just make sure that the datasource for the pivot is the name of the table, something like Table1 (not in code, just through the dialogue boxes). Then after updating the table you'll only have to refresh the pivot.

[QnA]

its a brand new set of data every week so I'd need to create from scratch each time.

i tried creating a data table each time as well, but i run into the same issue where it hard codes the table range in the macro.

[p45cal]

its a brand new set of data every week so I'd need to create from scratch each time.

Not necessarily; does the new set of data have the same headings?
If so running the blah macro in my last message should work (I derived where the pivot table is and where the source data is from your quoted code).

[QnA]

the issue is that there is no existing pivot table. i would run a report on a data set, then the following week, there is an entirely new file that i would need to run the report on again. there is no existing Pivot table.

[p45cal]

Change that line in your first post here to:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Sheets("Sheet1").Range("A1").CurrentRegion, Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14

but make sure you've a blank sheet2 before you start (and no other pivot table called PivotTable1 in the workbook)

[QnA]

You are some kind of wonderful P45Cal! That worked perfectly! Thank you!