Why there are 2 duplicate of the Pivot Cache?

[Jamualson]

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.

 

[Alex Blakenburg]

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

Dim MyTable As Range
Set MyTable = Range("Table1[#All]")

 

[Alex Blakenburg]

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]

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.