Class PivotCaches (Excel VBA)

The class PivotCaches represents the collection of memory caches from the PivotTable reports in a workbook. To use a PivotCaches class variable it first needs to be instantiated, for example


Dim pcs as PivotCaches
Set pcs = ActiveWorkbook.PivotCaches()

For Each

Here is an example of processing the PivotCaches items in a collection.


Dim pvtcac As PivotCache
For Each pvtcac In ActiveWorkbook.PivotCaches()
	
Next pvtcac

Count

Returns a Long value that represents the number of objects in the collection.


Dim lngCount As Long
lngCount = ActiveWorkbook.PivotCaches.Count

Create

Creates a new PivotCache.

The following two XlPivotTableSourceType constants are not supported when creating a PivotCache by using this method: xlPivotTable and xlScenario. A run-time error is returned if one of these two constants is supplied. The SourceData argument is required if SourceType isn't xlExternal. It should be passed a Range object (when SourceType is either xlConsolidation or xlDatabase) or a WorkbookConnection object (when SourceType is xlExternal). When passing a Range object, we recommend that you either use a string to specify the workbook, worksheet, and cell range, or set up a named range and pass the name as a string. Passing a Range object may cause "type mismatch" errors unexpectedly. When not supplied, the version of the PivotTable will be xlPivotTableVersion12. The use of the xlPivotTableVersionCurrent constant is not allowed and returns a run-time error if it is supplied.

Create (SourceType, SourceData, Version)


Dim pvtcacCreate As PivotCache
Set pvtcacCreate = ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation)

Arguments

The following argument is required

SourceType (XlPivotTableSourceType) - SourceType can be one of these XlPivotTableSourceType constants: xlConsolidation, xlDatabase, or xlExternal.


Possible values are

xlConsolidation Multiple consolidation ranges.
xlDatabase Microsoft Excel list or database.
xlExternal Data from another application.
xlPivotTable Same source as another PivotTable report.
xlScenario Data is based on scenarios created using the Scenario Manager.

Optional arguments

The following arguments are optional

SourceData - The data for the new PivotTable cache

Version (XlPivotTableVersionList) - Version of the PivotTable. Version can be one of the XlPivotTableVersionList constants.


Possible values are

xlPivotTableVersion10 Excel 2002
xlPivotTableVersion11 Excel 2003
xlPivotTableVersion12 Excel 2007
xlPivotTableVersion14 Excel 2010
xlPivotTableVersion15 Excel 2013
xlPivotTableVersion2000 Excel 2000
xlPivotTableVersionCurrent Provided only for backward compatibility

Item

Returns a single object from a collection.

Item (Index)

Index: The name or index number for the object.


Dim pvtcac As PivotCache
Set pvtcac = ActiveWorkbook.PivotCaches(Index:=1)