Vba Single Pivot Cache for multiple pivot tables

[Ninak]

Hi,
I am using below pivot table code to create multiple sheets from master data and respectively create pivot table on each worksheet. Taken data from below you tube channel. But it is creating multiple caches for each pivot table. I have to move the multiple sheets with pivot tables to another excel file. But after moving the master file size is increasing drastically. Can anyone help with the changes in this code or any new code to resolve this issue.

https://youtu.be/mCAtrCYyBCU

How to Create Pivot Table for All Worksheets with 1 Click - Part 2
Topic: How to create pivot table for all worksheets with 1 click Scenario: You want to auto select your data and create pivot table with macro Function: Macro Related Video: How to Create Pivot Table for All Worksheets with 1 Click - Part 1 https://youtu.be/mCAtrCYyBCU

Below is the code

Sub SplitandFilterSheetandCreatePivotTable() 'Step 1 - Name your ranges and Copy sheet 'Step 2 - Filter by Department and delete rows not applicable 'Step 3 - Loop until the end of the list Dim Splitcode As Range Sheets("Master").Select Set Splitcode = Range("Splitcode") For Each cell In Splitcode Sheets("Master").Copy After:=Worksheets(Sheets.Count) ActiveSheet.Name = cell.Value With ActiveWorkbook.Sheets(cell.Value).Range("MasterData") .AutoFilter Field:=6, Criteria1:="NOT EQUAL TO" & cell.Value, Operator:=xlFilterValues .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete End With ActiveSheet.AutoFilter.ShowAllData 'add in the creating pivot table code set On Error Resume Next 'select your dataset range for pivot table Range("MasterData").Select 'create pivot table ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ Range("MasterData")).CreatePivotTable _ TableDestination:=Range("L5"), TableName:="PivotTable1" With ActiveSheet.PivotTables("PivotTable1").PivotFields("Performance Rating") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields("EE ID") .Orientation = xlDataField .Position = 1 .Function = xlCount .Name = "Count of EE ID" End With With ActiveSheet.PivotTables("PivotTable1").PivotFields("Base Salary") .Orientation = xlDataField .Position = 2 .Function = xlAverage .Name = "Average of Base Salary" .NumberFormat = "#,##0" End With With ActiveSheet.PivotTables("PivotTable1").PivotFields("Country") .Orientation = xlPageField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields("Country") .PivotItems("Germany").Visible = False .PivotItems("UK").Visible = False .PivotItems("USA").Visible = False End With ActiveSheet.PivotTables("PivotTable1").PivotFields("Country"). _ EnableMultiplePageItems = True Next cell End Sub

 

[Domenic]

I'm not going to watch a video, nor am I going to try to make sense of the code you've supplied since it's not properly formatted and within code tags. So I'll provide you with a simple example, from which you should be able to amend for your needs. It simply creates a single pivot cache, and then it creates two pivot tables using the same pivot cache.

Option Explicit

Sub CreateMultiplePivotTables()

    'set the data for the source range
    Dim source_range As Range
    Set source_range = ActiveSheet.Range("A1").CurrentRegion
   
    'create the pivot cache
    Dim pivot_cache As PivotCache
    Set pivot_cache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=source_range)
   
    Dim current_sheet As Worksheet
    Dim current_pivottable As PivotTable
   
    'create a new worksheet for the first pivot table
    Set current_sheet = Worksheets.Add
   
    'create first pivot table
    Set current_pivottable = current_sheet.PivotTables.Add(PivotCache:=pivot_cache, TableDestination:=current_sheet.Range("A1"), TableName:="PivotTable1")
   
    'build the first pivot table
    With current_pivottable
        'etc
        '
        '
    End With
   
    'create another new worksheet for the second pivot table
    Set current_sheet = Worksheets.Add
   
    'create second pivot table
    Set current_pivottable = current_sheet.PivotTables.Add(PivotCache:=pivot_cache, TableDestination:=current_sheet.Range("A1"), TableName:="PivotTable2")
   
    'build the second pivot table
    With current_pivottable
        'etc
        '
        '
    End With
   
End Sub

Hope this helps!