Vba Single Pivot Cache for multiple pivot tables

Ninak

New Member
Joined
Apr 9, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,826
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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!
 

Forum statistics

Threads
1,148,274
Messages
5,745,797
Members
423,974
Latest member
highvoltageacdc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top