Why there are 2 duplicate of the Pivot Cache?

Jamualson

Board Regular
Joined
Feb 17, 2021
Messages
134
Office Version
  1. 2019
Platform
  1. Windows
I created a Pivot Table from a Table using basic Excel, so that's 1 Pivot cache
Then I created another one but using VBA, and now there are 2 Pivot Caches.

However, they use the same table. What is the cause of it? If I create more Pivot Tables from VBA, or from Excel, it doesn't increase the number of Pivot Caches anymore, it stops at 2.


Set PivotC= ActiveWorkbook.PivotCaches.Create (SourceType:=xlDatabase, SourceData:=MyTable)

Thank you very much.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
804
Office Version
  1. 365
Platform
  1. Windows
I suspect the datasource ranges aren't identical or weren't at the time of creation.
You might want to try running to code on the contextures site to list the data source ranges, to see if that sheds any light on it.
List Pivot Table Data Sources - Contextures

I assume you have a line that looks something like this in your code
VBA Code:
Dim MyTable As Range
Set MyTable = Range("Table1[#All]")
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
804
Office Version
  1. 365
Platform
  1. Windows
Grasping at straws here but Pivot Table Caches are changed by adding Grouping and Calculated Fields.
Is it possible that one of the pivots use these and the other does not (manually created vs VBA created) ?

Also you can of course get VBA to create the Pivot Table based on an existing Pivot Table in the workbook, so that it uses the same cache.

The same Contextures Link I gave has VBA code for the following:-
  • Create Pivot Table From an existing Pivot Cache
  • Remove Duplicate Pivot Caches
 

Jamualson

Board Regular
Joined
Feb 17, 2021
Messages
134
Office Version
  1. 2019
Platform
  1. Windows
Grasping at straws here but Pivot Table Caches are changed by adding Grouping and Calculated Fields.
Is it possible that one of the pivots use these and the other does not (manually created vs VBA created) ?

Also you can of course get VBA to create the Pivot Table based on an existing Pivot Table in the workbook, so that it uses the same cache.

The same Contextures Link I gave has VBA code for the following:-
  • Create Pivot Table From an existing Pivot Cache
  • Remove Duplicate Pivot Caches
Yes one contains grouping, nice tipp. Thank you very much, will take a look at it and test.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,785
Messages
5,655,293
Members
418,187
Latest member
polks111

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