Consolidate Pivot Table Cache - VBA

[tozelli]

Hi all,

First, sorry if the code isn't very simplified, still learning.

The pivot tables 'Adam' and 'Raewyn' use exactly the same database, therefore I want to make them use the same cache (will save me 20mb in the final version).
I have found a way to make all the pivot tables to use the same cache but not just two of them.
Notes:
Each pivot tables are in different sheets and the database is in another workbook.

Thanks in advance.

Dim Data_sht As Worksheet
Dim Data_Blair As Worksheet
Dim Pivot_Adam As Worksheet
Dim Pivot_Raewyn As Worksheet
Dim Pivot_Blair As Worksheet
Dim StartPoint As Range
Dim DataRange As Range
Dim StartPointBlair As Range
Dim DataRangeBlair As Range
Dim PivotAdam As String
Dim PivotRaewyn As String
Dim PivotBlair As String
Dim NewRange As String
Dim BlairRange As String

'Set Variables Equal to Data Sheet and Pivot Sheet
  Set Data_sht = Workbooks("Adam-Raewyn-Blair Base-en-nz").Worksheets("Adam and Raewyn")
  Set Data_Blair = Workbooks("Adam-Raewyn-Blair Base-en-nz").Worksheets("Blair")
  Set Pivot_Adam = ThisWorkbook.Worksheets("Adam")
  Set Pivot_Raewyn = ThisWorkbook.Worksheets("Raewyn")
  Set Pivot_Blair = ThisWorkbook.Worksheets("Blair")

'Enter in Pivot Table Name
  PivotAdam = "PivotTable1"
  PivotRaewyn = "PivotTable2"
  PivotBlair = "PivotTable3"

'Dynamically Retrieve Range Address of Data
  Set StartPoint = Data_sht.Range("A1")
  Set StartPointBlair = Data_Blair.Range("A1")
  Set DataRange = StartPoint.CurrentRegion
  Set DataRangeBlair = StartPointBlair.CurrentRegion
  
  NewRange = "'O:\Nz\Cognos Report Output\Prod\MySchool Parents\[Adam-Raewyn-Blair Base-en-nz.xlsx]Adam and Raewyn'!" & _
    DataRange.Address(ReferenceStyle:=xlR1C1)
  BlairRange = "'O:\Nz\Cognos Report Output\Prod\MySchool Parents\[Adam-Raewyn-Blair Base-en-nz.xlsx]Blair'!" & _
    DataRangeBlair.Address(ReferenceStyle:=xlR1C1)
    
'Change Pivot Table Data Source Range Address
  Pivot_Adam.PivotTables(PivotAdam).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=NewRange)
  Pivot_Raewyn.PivotTables(PivotRaewyn).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=NewRange)
  Pivot_Blair.PivotTables(PivotBlair).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=BlairRange)

[p45cal]

I can't see a question in your message?
Debra Dalgleish is quite an authority on all things Pivot, see here on reducing Pivot Cache instances: https://contextures.com/xlPivot11.html#removedups
and if looking to reduce file size, see: https://www.pivot-table.com/2015/01/...ot-table-file/