Consolidate Pivot Table Cache - VBA

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)

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I did it for you this time. Please read forum rules. Thanks