Multiple Pivot Table Using Same Data Source & Cache

Question

I'm working on automating data analysis & creation of pivots and charts for which I have Raw Data (441379 Rows, 40 columns).

1) Raw Data changes monthly, so I need a dynamic range.

2) I have to create around 20 Pivot Tables with the same data.

3) I'm defining range and pivot cache for every single pivot. A lot of time is consumed in reading data and executing every single time.

4) I need the flexibility of choosing a range for placing the pivot table as one sheet may have more than one pivot table.

5) I need the flexibility of creating a sheet and naming them for pivot as I will have around 20 sheets.

How can I define pivot source and pivot cache once in the workbook so I can use the same for all the pivot tables?

Below is the code which I'm using

' For Dynamic data range named "PvtData"

ActiveWorkbook.Names.Add Name:="PvtData", RefersToR1C1:= _
"=OFFSET('Data'!R1C1,0,0,COUNTA('Data'!C1),COUNTA('Data'!R1))"

' For creating Pivot using Dynamic data range named "PvtData"

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"PvtData").CreatePivotTable TableDestination:="", TableName:="PivotTable_UBD"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(7, 1)
ActiveSheet.Cells(7, 1).Select
ActiveSheet.PivotTables("PivotTable_UBD").SmallGrid = False
ActiveSheet.Name = "Utilization By Day"

Or

' For Dynamic data range named "PvtData"

ActiveWorkbook.Names.Add Name:="PvtData", RefersToR1C1:= _
"=OFFSET('Data'!R1C1,0,0,COUNTA('Data'!C1),COUNTA('Data'!R1))"

' For creating Pivot using Dynamic data range named "PvtData"

Set PivotCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:="PvtData")
Worksheets.Add
ActiveSheet.Name = "Utilization By Day"
ActiveWindow.DisplayGridlines = True

Set PivotTable = ActiveSheet.PivotTables.Add(PivotCache:=PivotCache, _
TableDestination:=Range("A1"), TableName:="PivotTable_UBD")

Ken Ken · Accepted Answer · 2019-05-15T20:51:19

I know this has been here a while and not sure you still need it answered.

Take your Raw data and turn it into a table.
Create a Pivot Table from the data source that is in table format. Once created from the table your pivot source becomes a dynamic source. As the table size changes the pivot source changes since it is referencing the table.
Copy the pivot table and paste the pivot table 19 times. Since they are all copies, they work from one cache. Once one pivot is refreshed all 20 are refreshed regardless of how many sheets they are on.

Multiple Pivot Table Using Same Data Source & Cache

1 Answers