Class PivotItem (Excel VBA)

The class PivotItem represents an item in a PivotTable field.

The classes PivotCell, PivotField and Range. give access to class PivotItem

Member in collection

Item

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

Dim pvti as PivotItem
Set pvti = ActiveCell.PivotItem

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

For Each

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

Dim pvtiCalculatedItem As PivotItem
For Each pvtiCalculatedItem In Application.CalculatedItems()
	
Next pvtiCalculatedItem

CalculatedItems.Add - Creates a new calculated item. Returns a PivotItem object.

CalculatedItems.Item - Returns a single object from a collection.

Delete - Deletes the object.

DrillTo - The DrillTo method supports drilling to a specified PivotField from a PivotItem.

PivotItemList.Item - Returns a single object from a collection.

PivotItems.Add - Creates a new PivotTable item.

PivotItems.Item - Returns a single object from a collection.

Properties

Value (Default member) - Returns or sets a String value that represents the name of the specified item in the PivotTable field.

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

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.

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

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

Formula returns or sets a String value that represents the object's formula in A1-style notation and in the language of the macro.

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.

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).

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.

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

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

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.

SourceName returns a value that represents the specified object's name as it appears in the original source data for the specified PivotTable report.

SourceNameStandard returns a String that represents the PivotTable items' source name in standard English (United States) format settings.

StandardFormula returns or sets a String specifying formulas with standard English (United States) formatting.

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.

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

PivotItems - A collection of all the PivotItem objects in a PivotTable field.

Range - Represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3D range.