tozelli
04-14-2020, 08:28 PM
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)
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)