PivotTable Object (Excel)

Office 2013 and later
GitHub-Mark-64px

Contribute to this content

Use GitHub to suggest and submit changes. See our guidelines for contributing to VBA documentation.

Represents a PivotTable report on a worksheet.

The PivotTable object is a member of the PivotTables collection. The PivotTables collection contains all the PivotTable objects on a single worksheet.

Because PivotTable report programming can be complex, it’s generally easiest to record PivotTable report actions and then revise the recorded code.

Use PivotTables(index), where index is the PivotTable index number or name, to return a single PivotTable object. The following example makes the field named year a row field in the first PivotTable report on Sheet3.

Worksheets("Sheet3").PivotTables(1) _ 
 .PivotFields("Year").Orientation = xlRowField

Name

ActiveFilters

Allocation

AllocationMethod

AllocationValue

AllocationWeightExpression

AllowMultipleFilters

AlternativeText

Application

CacheIndex

CalculatedMembers

CalculatedMembersInFilters

ChangeList

ColumnFields

ColumnGrand

ColumnRange

CompactLayoutColumnHeader

CompactLayoutRowHeader

CompactRowIndent

Creator

CubeFields

DataBodyRange

DataFields

DataLabelRange

DataPivotField

DisplayContextTooltips

DisplayEmptyColumn

DisplayEmptyRow

DisplayErrorString

DisplayFieldCaptions

DisplayImmediateItems

DisplayMemberPropertyTooltips

DisplayNullString

EnableDataValueEditing

EnableDrilldown

EnableFieldDialog

EnableFieldList

EnableWizard

EnableWriteback

ErrorString

FieldListSortAscending

GrandTotalName

HasAutoFormat

Hidden

HiddenFields

InGridDropZones

InnerDetail

LayoutRowDefault

Location

ManualUpdate

MDX

MergeLabels

Name

NullString

PageFieldOrder

PageFields

PageFieldStyle

PageFieldWrapCount

PageRange

PageRangeCells

Parent

PivotChart

PivotColumnAxis

PivotFormulas

PivotRowAxis

PivotSelection

PivotSelectionStandard

PreserveFormatting

PrintDrillIndicators

PrintTitles

RefreshDate

RefreshName

RepeatItemsOnEachPrintedPage

RowFields

RowGrand

RowRange

SaveData

SelectionMode

ShowDrillIndicators

ShowPageMultipleItemLabel

ShowTableStyleColumnHeaders

ShowTableStyleColumnStripes

ShowTableStyleLastColumn

ShowTableStyleRowHeaders

ShowTableStyleRowStripes

ShowValuesRow

Slicers

SmallGrid

SortUsingCustomLists

SourceData

SubtotalHiddenPageItems

Summary

TableRange1

TableRange2

TableStyle2

Tag

TotalsAnnotation

VacatedStyle

Value

Version

ViewCalculatedMembers

VisibleFields

VisualTotals

VisualTotalsForSets

Show: