Multiple Pivot Table using same cache

[predzer]

Hi Guys!

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 keeps changing monthly, so I need a dynamic range.

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

3) As of now I'm defining range and pivot cache for every single pivot I do, Because of that a lot of time is consumed in reading data and executing every single time.

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

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

Please Help me out by creating a efficient CODE to define pivot source and pivot cache just once in the workbook so that i can use the same for all the pivot tables, which will save a ton of my time.

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")


THANK YOU SO MUCH IN ADVANCE!

 

[RoryA]

Welcome to the forum.

Please take a minute to read the forum rules on cross-posting and comply with them. Thanks.

 

[predzer]

Hi RoryA,

My apologies. I did read through the cross-posting forum rules and was having thoughts before posting.

BUT I did search through the forum but could'nt find relevant post so went ahead and posted it. My concerns were either those codes in other post was using declarations which i am not well versed with, as you can see I haven't used any declarations in my code. So if i had to comply all my codes with declarations it would take quite a lot of time to revamp all the modules I have created so far. I have around 22 modules so far and looking to add another 15.

Thanks for your time.

 

[RoryA]

I don't think you understood the rule. You need to provide links here to your posts on other forums (eg Stack Overflow) on this topic.

 

[predzer]

I missed Rule #13 !

Also Posted on http://stackoverflow.com/questions/50967451/multiple-pivot-table-using-same-data-source-cache