Class PivotTable (Excel VBA)

The class PivotTable represents a PivotTable report on a worksheet.

The main procedures of class PivotTable are AddDataField, AddFields, PivotTables.Add and SlicerPivotTables.AddPivotTable

Set

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

Dim pvt as PivotTable
Set pvt = ActiveCell.PivotTable

The following procedures can be used to set variables of type PivotTable: PivotCache.SourceData, PivotCache.CreatePivotTable, PivotCell.Parent, PivotCell.PivotTable, PivotFields.Parent, PivotLayout.PivotTable, PivotTableChangeList.Parent, PivotTables.Item, PivotTables.Add, Range.PivotTable, SlicerPivotTables.Item, Worksheet.PivotTableWizard, Workbook.PivotTables, Worksheet.PivotTables and SlicerCache.PivotTables

For Each

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

Dim pvt As PivotTable
For Each pvt In Application.PivotTables
	
Next pvt

Themes

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

Add with its procedures AddDataField, AddFields, PivotTables.Add and SlicerPivotTables.AddPivotTable
Value with its procedures AllocationValue, RefreshDataSourceValues and Value
Column with its procedures ColumnFields, ColumnGrand, ColumnRange and PivotColumnAxis
Row with its procedures CompactRowIndent, PivotRowAxis, RowFields, RowGrand and RowRange
Formula with its procedures ConvertToFormulas, ListFormulas and PivotFormulas
Display/Show with its procedures DisplayContextTooltips, DisplayEmptyColumn, DisplayEmptyRow, DisplayErrorString, DisplayFieldCaptions, DisplayImmediateItems, DisplayMemberPropertyTooltips, DisplayNullString, ShowDrillIndicators and ShowValuesRow
Enable with its procedures EnableDataValueEditing, EnableDrilldown, EnableFieldDialog, EnableFieldList, EnableWizard and EnableWriteback
Sort/Order with its procedures FieldListSortAscending and SortUsingCustomLists
Name with its procedures GrandTotalName and RefreshName
Print with its procedures PageFieldOrder, PageFields, PageFieldStyle, PageFieldWrapCount, PageRange, PageRangeCells, PrintDrillIndicators, PrintTitles, RepeatItemsOnEachPrintedPage, ShowPageMultipleItemLabel, ShowPages and SubtotalHiddenPageItems

Methods

Other Methods

AllocateChanges - Performs a writeback operation for all edited cells in a PivotTable report based on an OLAP data source.

CalculatedFields - Returns a CalculatedFields collection that represents all the calculated fields in the specified PivotTable report.

ChangeConnection - Changes the connection of the specified PivotTable object.

ChangePivotCache - Changes the PivotCache object of the specified PivotTable.

ClearAllFilters - The ClearAllFilters method deletes all filters currently applied to the PivotTable. This includes deleting all filters in the PivotFilters collection, removing any manual filtering applied, and setting all PivotFields in the Report Filter area to the default item.

ClearTable - The ClearTable method is used for clearing a PivotTable. Clearing PivotTables includes removing all the fields and deleting all filtering and sorting applied to the PivotTables. This method resets the PivotTable to the state it had right after it was created, before any fields were added to it.

CommitChanges - Performs a commit operation on the data source of a PivotTable report based on an OLAP data source.

CreateCubeFile - Creates a cube file from a PivotTable report connected to an Online Analytical Processing (OLAP) data source.

DiscardChanges - Discards all changes in the edited cells of a PivotTable report based on an OLAP data source.

DrillDown - Enables you to drill down into the data within an OLAP-based or PowerPivot-based cube hierarchy.

DrillTo - Enables you to drill to a location within an OLAP-based or PowerPivot-based cube hierarchy.

DrillUp - Enables you to drill up into the data within an OLAP-based or PowerPivot-based cube hierarchy.

GetData - Returns the value for the data filed in a PivotTable.

GetPivotData - Returns a Range object with information about a data item in a PivotTable report.

PivotCache - Returns a PivotCache object that represents the cache for the specified PivotTable report.

PivotFields - Returns an pivotfields that represents either a single PivotTable field (a PivotField pivotfields) or a collection of both the visible and hidden fields (a PivotFields pivotfields) in the PivotTable report. Read-only.

PivotSelect - Selects part of a PivotTable report.

PivotTableWizard - Creates and returns a PivotTable object. This method doesn't display the PivotTable Wizard. This method isn't available for OLE DB data sources. Use the Add method to add a PivotTable cache, and then create a PivotTable report based on the cache.

PivotValueCell - Retrieve the PivotValueCell object for a given PivotTable provided certain row and column indices.

RefreshTable - Refreshes the PivotTable report from the source data. Returns True if it's successful.

RepeatAllLabels - Specifies whether to repeat item labels for all PivotFields in the specified PivotTable.

RowAxisLayout - This method is used for simultaneously setting layout options for all existing PivotFields.

SubtotalLocation - This method changes the subtotal location for all existing PivotFields. Changing the subtotal location has an immediate visual effect only for fields in outline form, but it will be set for fields in tabular form as well.

Update - Updates the PivotTable report.

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

SlicerPivotTables.RemovePivotTable - Removes a reference to a PivotTable from the SlicerPivotTables collection.

Properties

ActiveFilters indicates the currently active filter in the specified PivotTable.

Allocation returns or sets whether to run an UPDATE CUBE statement for each cell that is edited, or only when the user chooses to calculate changes when performing what-if analysis on a PivotTable based on an OLAP data source.

AllocationMethod returns or sets the method to use to allocate values when performing what-if analysis on a PivotTable report based on an OLAP data source.

AllocationWeightExpression returns or sets the MDX weight expression to use when performing what-if analysis on a PivotTable report based on an OLAP data source.

AllowMultipleFilters sets or retrieves a value that indicates whether a PivotField can have multiple filters applied to it at the same time.

AlternativeText returns or sets the descriptive (alternative) text string for the specified PivotTable.

CacheIndex returns or sets the index number of the PivotTable cache.

CalculatedMembers returns a CalculatedMembers collection representing all the calculated members and calculated measures for an OLAP PivotTable.

CalculatedMembersInFilters returns or sets whether to evaluate calculated members from OLAP servers in filters.

ChangeList returns the PivotTableChangeList collection that represents the list of changes that have been made to the specified PivotTable based on an OLAP data source.

CompactLayoutColumnHeader specifies the caption that is displayed in the column header of a PivotTable when in compact row layout form.

CompactLayoutRowHeader specifies the caption that is displayed in the row header of a PivotTable when in compact row layout form.

CubeFields returns the CubeFields collection. Each CubeField object contains the properties of the cube field element.

DataBodyRange returns a Range object that represents the range of values in a PivotTable.

DataFields returns an pivotfield that represents either a single PivotTable field (a PivotField pivotfield) or a collection of all the fields (a PivotFields pivotfield) that are currently shown as data fields. Read-only.

DataLabelRange returns a Range object that represents the range that contains the labels for the data fields in the PivotTable report.

DataPivotField returns a PivotField object that represents all the data fields in a PivotTable.

ErrorString returns or sets a String value that represents the string displayed in cells that contain errors when the DisplayErrorString property is True.

HasAutoFormat true if the PivotTable report is automatically formatted when it's refreshed or when fields are moved.

Hidden checks whether the PivotTable exists at the worksheet level.

HiddenFields returns an pivotfield that represents either a single PivotTable field (a PivotField pivotfield) or a collection of all the fields (a PivotFields pivotfield) that are currently not shown as row, column, page, or data fields. Read-only.

InGridDropZones this property is used to toggle in-grid drop zones for a PivotTable object. In some cases, it also affects the layout of the PivotTable.

InnerDetail returns or sets the name of the field that will be shown as detail when the ShowDetail property is True for the innermost row or column field.

LayoutRowDefault this property specifies the layout settings for PivotFields when they are added to the PivotTable for the first time.

Location gets or sets a String that represents the top-left cell in the body of the specified PivotTable object.

ManualUpdate true if the PivotTable report is recalculated only at the user's request. The default value is False.

MDX returns a String indicating the Multidimensional Expression (MDX) that would be sent to the provider to populate the current PivotTable view.

MergeLabels true if the specified PivotTable report's outer-row item, column item, subtotal, and grand total labels use merged cells.

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

NullString returns or sets the string displayed in cells that contain null values when the DisplayNullString property is True. The default value is an empty string ("").

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

PivotChart returns a Shape object that represents the standalone PivotChart for the specified hidden PivotTable report.

PivotSelection returns or sets the PivotTable selection in standard PivotTable report selection format.

PivotSelectionStandard returns or sets a String indicating the PivotTable selection in standard PivotTable report format using English (United States) settings.

PreserveFormatting true if formatting is preserved when the report is refreshed or recalculated by operations such as pivoting, sorting, or changing page field items.

RefreshDate returns the date on which the PivotTable report was last refreshed.

SaveData true if data for the PivotTable report is saved with the workbook. False if only the report definition is saved.

SelectionMode returns or sets the PivotTable report structured selection mode.

ShowTableStyleColumnHeaders the ShowTableStyleColumnHeaders property is set to True if the column headers should be displayed in the PivotTable.

ShowTableStyleColumnStripes the ShowTableStyleColumnStripes property displays banded columns in which even columns are formatted differently from odd columns. This makes PivotTables easier to read.

ShowTableStyleLastColumn returns or sets if the last column is displayed for the specified PivotTable object.

ShowTableStyleRowHeaders the ShowTableStyleRowHeaders property is set to True if the row headers should be displayed in the PivotTable.

ShowTableStyleRowStripes the ShowTableStyleRowStripes property displays banded rows in which even rows are formatted differently from odd rows. This makes PivotTables easier to read.

Slicers returns the Slicers collection for the specified PivotTable.

SmallGrid true if Microsoft Excel uses a grid that's two cells wide and two cells deep for a newly created PivotTable report. False if Excel uses a blank stencil outline.

SourceData returns the data source for the PivotTable report, as shown in the following table.

Summary returns or sets the description associated with the alternative text string for the specified PivotTable.

TableRange1 returns a Range object that represents the range containing the entire PivotTable report, but doesn't include page fields.

TableRange2 returns a Range object that represents the range containing the entire PivotTable report, including page fields.

TableStyle2 the TableStyle2 property specifies the PivotTable style currently applied to the PivotTable.

Tag returns or sets a string saved with the PivotTable report.

TotalsAnnotation true if an asterisk (*) is displayed next to each subtotal and grand total value in the specified PivotTable report if the report is based on an OLAP data source. The default value is True.

VacatedStyle returns or sets the style applied to cells vacated when the PivotTable report is refreshed. The default value is a null string (no style is applied by default).

Version returns an XlPivotTableVersionList value that represents the Microsoft Excel version number.

ViewCalculatedMembers when set to True (default), calculated members for Online Analytical Processing (OLAP) PivotTables can be viewed.

VisibleFields returns an pivotfield that represents either a single field in a PivotTable report (a PivotField pivotfield) or a collection of all the visible fields (a PivotFields pivotfield). Visible fields are shown as row, column, page or data fields. Read-only.

VisualTotals true (default) to enable Online Analytical Processing (OLAP) PivotTables to retotal after an item has been hidden from view.

VisualTotalsForSets returns or sets whether to include filtered items in the totals of named sets for the specified PivotTable.

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

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

SlicerPivotTables.Count returns the number of objects in the collection.

SlicerPivotTables.Item returns a single PivotTable object from the collection.

SlicerPivotTables.Parent returns the SlicerCache object that is the parent of the specified SlicerPivotTables collection. Read-only.