Class PivotTable (Excel VBA)
The class PivotTable represents a PivotTable report on a worksheet.
The classes PivotCache, PivotCell, PivotLayout, Range and Worksheet. give access to class PivotTable
Dim pvt as PivotTable
Set pvt = ActiveCell.PivotTable
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
Methods
GetPivotData - Returns a Range object with information about a data item in a PivotTable report.
Dim rngGetPivotData As Range
Set rngGetPivotData = ActiveCell.PivotTable.GetPivotData()AddDataField - Adds a data field to a PivotTable report. Returns a PivotField object that represents the new data field.
AddFields - Adds row, column, and page fields to a PivotTable report or PivotChart report.
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.
ConvertToFormulas - The ConvertToFormulas method is used for converting a PivotTable to cube formulas.
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.
ListFormulas - Creates a list of calculated PivotTable items and fields on a separate worksheet.
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) or a collection of both the visible and hidden fields (a PivotFields collection) 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.
RefreshDataSourceValues - Retrieves the current values from the data source for all edited cells in a PivotTable report that is in writeback mode.
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.
ShowPages - Creates a new PivotTable report for each item in the page field. Each new report is created on a new worksheet.
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.
Properties
Value (Default member) - Returns or sets a String value that represents the name of the PivotTable report.
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.
AllocationValue returns or sets the value to allocate 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.
ColumnFields returns an pivotfield that represents either a single PivotTable field (a PivotField) or a collection of all the fields (a PivotFields collection) that are currently shown as column fields. Read-only.
ColumnGrand true if the PivotTable report shows grand totals for columns.
ColumnRange returns a Range object that represents the range that contains the column area in the PivotTable report.
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.
CompactRowIndent returns or sets the indent increment for PivotItems when compact row layout form is turned on.
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) or a collection of all the fields (a PivotFields collection) 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.
DisplayContextTooltips controls whether tooltips are displayed for PivotTable cells.
DisplayEmptyColumn returns True when the non-empty MDX keyword is included in the query to the OLAP provider for the value axis. The OLAP provider will not return empty columns in the result set. Returns False when the non-empty keyword is omitted.
DisplayEmptyRow returns True when the non-empty MDX keyword is included in the query to the OLAP provider for the category axis. The OLAP provider will not return empty rows in the result set. Returns False when the non-empty keyword is omitted.
DisplayErrorString true if the PivotTable report displays a custom error string in cells that contain errors. The default value is False.
DisplayFieldCaptions controls whether filter buttons and PivotField captions for rows and columns are displayed in the grid.
DisplayImmediateItems returns or sets a Boolean that indicates whether items in the row and column areas are visible when the data area of the PivotTable is empty. Set this property to False to hide the items in the row and column areas when the data area of the PivotTable is empty. The default value is True.
DisplayMemberPropertyTooltips controls whether to display member properties in tooltips.
DisplayNullString true if the PivotTable report displays a custom string in cells that contain null values. The default value is True.
EnableDataValueEditing true to disable the alert for when the user overwrites values in the data area of the PivotTable. True also allows the user to change data values that previously could not be changed. The default value is False.
EnableDrilldown true if drilldown is enabled. The default value is True.
EnableFieldDialog true if the PivotTable Field dialog box is available when the user double-clicks the PivotTable field. The default value is True.
EnableFieldList false to disable the ability to display the field list for the PivotTable. If the field list was already being displayed, it disappears. The default value is True.
EnableWizard true if the PivotTable Wizard is available. The default value is True.
EnableWriteback returns or sets whether writing back to the data source is enabled for the specified PivotTable. The default value is False.
ErrorString returns or sets a String value that represents the string displayed in cells that contain errors when the DisplayErrorString property is True.
FieldListSortAscending controls the sort order of fields in the PivotTable Field List. When this property is set to True, the fields are sorted in ascending order. When it is set to False, the fields are sorted in data source order.
GrandTotalName returns or sets the text string label that is displayed in the grand total column or row heading in the specified PivotTable report. The default value is the string Grand Total.
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) or a collection of all the fields (a PivotFields collection) 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 ("").
PageFieldOrder returns or sets the order in which page fields are added to the PivotTable report's layout. Can be one of the following XlOrder constants: xlDownThenOver or xlOverThenDown. The default constant is xlDownThenOver.
PageFields returns an pivotfield that represents either a single PivotTable field (a PivotField) or a collection of all the fields (a PivotFields collection) that are currently showing as page fields. Read-only.
PageFieldStyle returns or sets the style used in the bound page field area. The default value is a null string (no style is applied by default).
PageFieldWrapCount returns or sets the number of page fields in each column or row in the PivotTable report.
PageRange returns a Range object that represents the range that contains the page area in the PivotTable report.
PageRangeCells returns a Range object that represents only the cells in the specified PivotTable report that contain the page fields and item drop-down lists.
PivotChart returns a Shape object that represents the standalone PivotChart for the specified hidden PivotTable report.
PivotColumnAxis returns a PivotAxis object representing the entire column axis.
PivotFormulas returns a PivotFormulas object that represents the collection of formulas for the specified PivotTable report.
PivotRowAxis returns a PivotAxis object representing the entire row axis.
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.
PrintDrillIndicators specifies whether drill indicators are printed with the PivotTable.
PrintTitles true if the print titles for the worksheet are set based on the PivotTable report. False if the print titles for the worksheet are used. The default value is False.
RefreshDate returns the date on which the PivotTable report was last refreshed.
RefreshName returns the name of the person who last refreshed the PivotTable report data.
RepeatItemsOnEachPrintedPage true if row, column, and item labels appear on the first row of each page when the specified PivotTable report is printed. False if labels are printed only on the first page. The default value is True.
RowFields returns an pivotfield that represents either a single field in a PivotTable report (a PivotField) or a collection of all the fields (a PivotFields collection) that are currently showing as row fields. Read-only.
RowGrand true if the PivotTable report shows grand totals for rows.
RowRange returns a Range object that represents the range including the row area on the PivotTable report.
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.
ShowDrillIndicators the ShowDrillIndicators property is used for toggling the display of drill indicators in the PivotTable.
ShowPageMultipleItemLabel when set to True (default), "(Multiple Items)" will appear in the PivotTable cell on the worksheet whenever items are hidden and an aggregate of non-hidden items is shown in the PivotTable view.
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.
ShowValuesRow returns or sets whether the values row is displayed.
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.
SortUsingCustomLists the SortUsingCustomLists property controls whether custom lists are used for sorting items of fields, both initially when the PivotField is initialized and the PivotItems are ordered by their captions, and later when the user applies a sort.
SourceData returns the data source for the PivotTable report, as shown in the following table.
SubtotalHiddenPageItems true if hidden page field items in the PivotTable report are included in row and column subtotals, block totals, and grand totals. The default value is False.
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 ) or a collection of all the visible fields (a PivotFields collection). 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.
Related Classes
CalculatedFields - A collection of PivotField objects that represents all the calculated fields in the specified PivotTable report.
CalculatedMembers - A collection of all the CalculatedMember objects on the specified PivotTable.
CubeFields - A collection of all CubeField objects in a PivotTable report that is based on an OLAP cube. Each CubeField object represents a hierarchy or measure field from the cube.
PivotAxis - The PivotAxis object is used for asymmetric drilling in a PivotTable.
PivotCache - Represents the memory cache for a PivotTable report.
PivotField - Represents a field in a PivotTable report.
PivotFields - A collection of all the PivotField objects in a PivotTable report.
PivotFilters - The PivotFilters object is a collection of PivotFilter objects.
PivotFormulas - Represents the collection of formulas for a PivotTable report. Each formula is represented by a PivotFormula object.
PivotTableChangeList - Represents the list of changes a user has made to value cells in a PivotTable report based on an OLAP data source.
PivotValueCell - Provides a way to expose values of cells in the case that actual cells (Range objects) are not available.
Range - Represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3D range.
Shape - Represents an object in the drawing layer, such as an AutoShape, freeform, OLE object, or picture.
Slicers - A collection of Slicer objects.