PivotItem and PivotItems Members

Use the PivotItems collection to get the items in a pivot field. Use the PivotField object’s PivotItems property to get a reference to this collection. Use the PivotItem object to get information about an item. The PivotItems collection and PivotItem object have the following members:

1 Collection only

2 Object and collection

Add 1

Application 2

Caption

ChildItems

Count 1

Creator 2

DataRange

Delete

DrilledDown

Formula

IsCalculated

Item 1

LabelRange

Name

Parent 2

ParentItem

ParentShowDetail

Position

RecordCount

ShowDetail

SourceName

SourceNameStandard

StandardFormula

Value

Visible

 

PivotItem objects represent the individual values stored in each pivot field. The following code displays a pivot table’s data hierarchically in the Immediate window:

Sub ListAllItems(  )
    Dim pt As PivotTable, pf As PivotField, _
      pi1 As PivotItem, pi2 As PivotItem
    ' Get pivot table.
    Set pt = Worksheets("BookSales").PivotTables(1)
    ' Show table name.
    Debug.Print pt.name
    For Each pf In pt.PivotFields
        ' Show each field name.
        Debug.Print , pf.name
        For Each pi1 In pf.PivotItems
            ' Show each item value.
            Debug.Print , , pi1.name
            For Each pi2 In pi1.ChildItems
                ' Show subitems (not available for OLAP).
                Debug.Print , , , pi2.name
            Next
        Next
    Next
End Sub

Get Programming Excel with VBA and .NET now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.