Class PivotItem (Excel VBA)

The class PivotItem represents an item in a PivotTable field.

The main procedures of class PivotItem are CalculatedItems.Add, Delete and PivotItems.Add

Set

To use a PivotItem class variable it first needs to be instantiated, for example

Dim strName As String: strName = 
Dim strFormula As String: strFormula = 
Dim pvtf As PivotField: Set pvtf = 
Dim pvti As PivotItem
Set pvti = pvtf.CalculatedItems.Add(Name:=strName, Formula:=strFormula)

The following procedures can be used to set variables of type PivotItem: PivotField.CalculatedItems, CalculatedItems.Item, CalculatedItems.Add, PivotCell.PivotItem, PivotField.ChildItems, PivotField.CurrentPage, PivotField.HiddenItems, PivotField.ParentItems, PivotField.VisibleItems, ChildItems, ParentItem, PivotItemList.Item, PivotItems.Item, Range.PivotItem, PivotCell.ColumnItems, PivotCell.RowItems, PivotField.ChildItems, PivotField.HiddenItems, PivotField.ParentItems, PivotField.PivotItems, PivotField.VisibleItems and ChildItems

For Each

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

Dim pvtf As PivotField: Set pvtf = 
Dim pvtiCalculatedItem As PivotItem
For Each pvtiCalculatedItem In pvtf.CalculatedItems()
	
Next pvtiCalculatedItem

Themes

Some procedures in this class have been grouped together in themes and are described on separate theme pages

Add with its procedures CalculatedItems.Add and PivotItems.Add
Formula with its procedures Formula and StandardFormula
Name with its procedures SourceName and SourceNameStandard

Methods

These are the main methods of the PivotItem class

Delete - Deletes the object.

Properties

CalculatedItems.Count returns a Long value that represents the number of objects in the collection.

CalculatedItems.Parent returns the parent object for the specified object. Read-only.

Caption returns a String value that represents the label text for the pivot item.

ChildItems returns an object that represents either a single PivotTable item (a PivotItem object) or a collection of all the items (a PivotItems object) that are group children in the specified field, or children of the specified item.

Set nwSheet = Worksheets.Add 
nwSheet.Activate 
Set pvtTable = Worksheets("Sheet2").Range("A1").PivotTable 
rw = 0 
For Each pvtItem In _ 
 pvtTable.PivotFields("product") 
 .PivotItems("vegetables").ChildItems 
 rw = rw + 1 
 nwSheet.Cells(rw, 1).Value = pvtItem.Name 
Next pvtItem

DataRange returns a Range object as shown in the following table.

Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable 
Worksheets("Sheet1").Activate 
pvtTable.PivotFields("REGION").DataRange.Select

DrilledDown true if the flag for the specified PivotTable field or PivotTable item is set to "drilled" (expanded or visible).

ActiveSheet.PivotTables("PivotTable3") _ 
 .PivotFields("state").DrilledDown = False

IsCalculated true if the PivotTable item is a calculated field or item.

LabelRange returns a Range object that represents all the cells in the PivotTable report that contain the item.

Name returns or sets a String value representing the name of the object.

Parent returns the parent object for the specified object.

ParentItem returns a PivotItem object that represents the parent PivotTable item in the parent PivotField object (the field must be grouped so that it has a parent).

Worksheets("Sheet1").Activate 
MsgBox "This item is a subitem of " & _ 
 ActiveCell.PivotItem.ParentItem.Name

ParentShowDetail true if the specified item is showing because one of its parents is showing detail. False if the specified item isn't showing because one of its parents is hiding detail. This property is available only if the item is grouped.

Worksheets("Sheet1").Activate 
Set pvtItem = ActiveCell.PivotItem 
If pvtItem.ParentShowDetail = True Then 
 MsgBox "Parent item is showing detail" 
End If

Position returns or sets a Long value that represents the position of the item in its field, if the item is currently showing.

Worksheets("Sheet1").Activate 
MsgBox "The active item is in position number " & _ 
 ActiveCell.PivotItem.Position

RecordCount returns the number of records in the PivotTable cache or the number of cache records that contain the specified item.

MsgBox Worksheets(1).PivotTables("Pivot1") _ 
 .PivotFields("Product").PivotItems("Kiwi").RecordCount

ShowDetail true if the outline is expanded for the specified range (so that the detail of the column or row is visible). The specified range must be a single summary column or row in an outline. Read/write Variant. For the PivotItem object (or the Range object if the range is in a PivotTable report), this property is set to True if the item is showing detail.

Value returns or sets a String value that represents the name of the specified item in the PivotTable field.

Visible returns or sets a Boolean value that determines whether the object is visible.

PivotItemList.Count returns a Long value that represents the number of objects in the collection.

PivotItemList.Parent returns the parent object for the specified object. Read-only.

PivotItems.Count returns a Long value that represents the number of objects in the collection.

PivotItems.Parent returns the parent object for the specified object.