PivotTables Collection Object

Description

A collection of all the PivotTable objects on the specified worksheet.

Using the PivotTables Collection

Use the PivotTables method to return the PivotTables collection. The following example displays the number of PivotTables on Sheet3.

MsgBox Worksheets("Sheet3").PivotTables.Count

Use the PivotTableWizard method to create a new PivotTable and add it to the collection. The following example creates a new PivotTable from a Microsoft Excel database (contained in the range A1:C100).

ActiveSheet.PivotTableWizard xlDatabase, Range("A1:C100")

Use PivotTables(index), where index is the PivotTable index number or name, to return a single PivotTable object. The following example makes the Year field a row field in PivotTable one on Sheet3.

Worksheets("Sheet3").PivotTables(1) _
    .PivotFields("Year").Orientation = xlRowField

Remarks

Because PivotTable programming can be complex, it's generally easiest to record PivotTable actions and then revise the recorded code. To record a macro, point to Macro on the Tools menu and click Record New Macro.

Properties

Application property, Count property, Creator property, Parent property.

Methods

Item method (PivotTables collection).