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

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

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)