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 pvtcacPivotCache As PivotCache: Set pvtcacPivotCache = 
Dim ptsTableDestination As PivotTables: Set ptsTableDestination = 
Dim wb As Workbook: Set wb = 
Dim pvt As PivotTable
Set pvt = wb.PivotTables.Add(PivotCache:=pvtcacPivotCache, TableDestination:=ptsTableDestination, TableName:="")

The following procedures can be used to set variables of type PivotTable: PivotCache.SourceData, PivotCache.CreatePivotTable, 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 wb As Workbook: Set wb = 
Dim pvtPivotTable As PivotTable
For Each pvtPivotTable In wb.PivotTables
	
Next pvtPivotTable

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

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

Sub UseCreateCubeFile() 
 
 ActiveSheet.PivotTables(1).CreateCubeFile _ 
 File:="C:\CustomCubeFile", Properties:=False 
 
End Sub

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

ActiveSheet.PivotTables("PivotTable1").DrillDown ActiveSheet.PivotTables( _
      "PivotTable1").PivotFields("[Customer].[Customer Geography].[Country]"). _
      PivotItems("[Customer].[Customer Geography].[Country].&[Australia]"), _
      ActiveSheet.PivotTables("PivotTable1").PivotRowAxis.PivotLines(1)

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.

ActiveSheet.PivotTables("PivotTable1").DrillUp ActiveSheet.PivotTables( _
      "PivotTable1").PivotFields("[Customer].[Customer Geography].[Postal Code]"). _
      PivotItems( _
      "[Customer].[Customer Geography].[Postal Code].&[2450]&[Coffs Harbour]"), _
      ActiveSheet.PivotTables("PivotTable1").PivotRowAxis.PivotLines(1)

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

Sub UseGetPivotData() 
 
 Dim rngTableItem As Range 
 
 ' Get PivotData for the quantity of chairs in the warehouse. 
 Set rngTableItem = ActiveCell. _ 
 PivotTable.GetPivotData("Quantity", "Warehouse", "Chairs") 
 
 MsgBox "The quantity of chairs in the warehouse is: " & rngTableItem.Value 
 
End Sub

PivotSelect - Selects part of a PivotTable report.

Worksheets(1).PivotTables(1).PivotSelect "date[All]", xlLabelOnly

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.

Sub TestEquality()
Dim X As Double
Dim Y As Double

'This code assumes you have a Standalone PivotChart on one of the worksheets
X = ThisWorkbook.PivotTables(1).PivotValueCell(1, 1).Value
Y = ThisWorkbook.PivotTables(1).PivotValueCell(1, 2).Value

If X > Y Then
MsgBox "X is greater than Y"
Else
MsgBox "Y is greater than X"
End If
End Sub

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.

Worksheets(1).PivotTables("Pivot1").CacheIndex = _ 
 Worksheets(1).PivotTables("Pivot2").CacheIndex

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

Sub UseCalculatedMember() 
 
 Dim pvtTable As PivotTable 
 
 Set pvtTable = ActiveSheet.PivotTables(1) 
 
 ' Add the calculated member. 
 pvtTable.CalculatedMembers.Add Name:="[Beef]", _ 
 Formula:="'{[Product].[All Products].Children}'", _ 
 Type:=xlCalculatedSet 
 
End Sub

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.

Set objNewSheet = Worksheets.Add 
objNewSheet.Activate 
intRow = 1 
For Each objCubeFld In Worksheets("Sheet1").PivotTables(1).CubeFields 
 If objCubeFld.Orientation = xlDataField Then 
 objNewSheet.Cells(intRow, 1).Value = objCubeFld.Name 
 intRow = intRow + 1 
 End If 
Next objCubeFld

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.

Set nwSheet = Worksheets.Add 
nwSheet.Activate 
Set pvtTable = Worksheets("Sheet2").Range("A1").PivotTable 
rw = 0 
For Each pvtField In pvtTable.DataFields 
 rw = rw + 1 
 nwSheet.Cells(rw, 1).Value = pvtField.Name 
Next pvtField

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

Worksheets("Sheet1").Activate 
Range("A3").Select 
ActiveCell.PivotTable.DataLabelRange.Select

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

Sub UseDataPivotField() 
 
 Dim pvtTable As PivotTable 
 
 Set pvtTable = ActiveSheet.PivotTables(1) 
 
 ' Move second PivotItem to the first position in PivotTable. 
 pvtTable.DataPivotField.PivotItems(2).Position = 1 
 
End Sub

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

With Worksheets(1).PivotTables("Pivot1") 
 .ErrorString = "-" 
 .DisplayErrorString = True 
End With

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.

Set nwSheet = Worksheets.Add 
nwSheet.Activate 
Set pvtTable = Worksheets("Sheet2").Range("A1").PivotTable 
rw = 0 
For Each pvtField In pvtTable.HiddenFields 
 rw = rw + 1 
 nwSheet.Cells(rw, 1).Value = pvtField.Name 
Next pvtField

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.

Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable 
MsgBox pvtTable.InnerDetail

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.

Worksheets(1).PivotTables("Pivot1").ManualUpdate = True

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

Sub CheckMDX() 
 
 Dim pvtTable As PivotTable 
 
 Set pvtTable = ActiveSheet.PivotTables(1) 
 
 MsgBox "The MDX string for the PivotTable is: " & _ 
 pvtTable.MDX 
 
End Sub

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

Worksheets(1).PivotTables(1).MergeLabels = True

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

With Worksheets(1).PivotTables("Pivot1") 
 .NullString = "NA" 
 .DisplayNullString = True 
End With

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.

Worksheets(1).PivotTables(1).PivotSelection = "Salesman[Bob]"

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

Sub CheckPivotSelectionStandard() 
 
 Dim pvtTable As PivotTable 
 
 Set pvtTable = ActiveSheet.PivotTables(1) 
 
 pvtTable.PivotSelectionStandard = "1.57" 
 Selection.Insert 
 
End Sub

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

Worksheets(1).PivotTables("Pivot1").PreserveFormatting = True

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

Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable 
dateString = Format(pvtTable.RefreshDate, "Long Date") 
MsgBox "The data was last refreshed on " & dateString

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

Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable 
pvtTable.SaveData = True

SelectionMode returns or sets the PivotTable report structured selection mode.

Application.PivotTableSelection = True 
Worksheets(1).PivotTables(1).SelectionMode = xlDataOnly

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.

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal) 
 .Connection = _ 
 "OLEDB;Provider=MSOLAP;Location=srvdata;Initial Catalog=National" 
 .MaintainConnection = True 
 .CreatePivotTable TableDestination:=Range("A3"), _ 
 TableName:= "PivotTable1" 
End With 
With ActiveSheet.PivotTables("PivotTable1") 
 .SmallGrid = False 
 .PivotCache.RefreshPeriod = 0 
 With .CubeFields("[state]") 
 .Orientation = xlColumnField 
 .Position = 0 
 End With 
 With .CubeFields("[Measures].[Count Of au_id]") 
 .Orientation = xlDataField 
 .Position = 0 
 End With 
End With

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

Set newSheet = ActiveWorkbook.Worksheets.Add 
sdArray = Worksheets("Sheet1").UsedRange.PivotTable.SourceData 
For i = LBound(sdArray) To UBound(sdArray) 
 newSheet.Cells(i, 1) = sdArray(i) 
Next i 

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.

Worksheets("Sheet1").Activate 
Range("A3").PivotTable.TableRange1.Select

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

Worksheets("Sheet1").Activate 
Range("A3").PivotTable.TableRange2.Select 

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

Sub ApplyingStyle() 
 
 ActiveSheet.PivotTables("PivotTable1").TableStyle2 = "PivotStyleLight17" 
 
End Sub

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

Worksheets(1).PivotTables("Pivot1").Tag = "Product Sales by Region"

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.

ActiveSheet.PivotTables(1).TotalsAnnotation = False

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

Worksheets(1).PivotTables("Pivot1").VacatedStyle = "BlackAndBlue"

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.

Sub CheckViewCalculatedMembers() 
 
 Dim pvtTable As PivotTable 
 
 Set pvtTable = ActiveSheet.PivotTables(1) 
 
 ' Determine if calculated members can be viewed. 
 If pvtTable.ViewCalculatedMembers = True Then 
 MsgBox "Calculated members can be viewed." 
 Else 
 MsgBox "Calculated members cannot be viewed." 
 End If 
 
End Sub

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.

Set nwSheet = Worksheets.Add 
nwSheet.Activate 
Set pvtTable = Worksheets("Sheet2").Range("A1").PivotTable 
rw = 0 
For Each pvtField In pvtTable.VisibleFields 
 rw = rw + 1 
 nwSheet.Cells(rw, 1).Value = pvtField.Name 
Next pvtField

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

Sub CheckVisualTotals() 
 
 Dim pvtTable As PivotTable 
 
 Set pvtTable = ActiveSheet.PivotTables(1) 
 
 ' Determine if visual totals is enabled for OLAP PivotTables. 
 If pvtTable.VisualTotals = True Then 
 MsgBox "Ability enabled to re-total after an item " & _ 
 "has been hidden from view." 
 Else 
 MsgBox "Unable to re-total items not hidden from view." 
 End If 
 
End Sub

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.