VBA Pivot Table Methods. The Pivot Table object is a member of the Pivot-tables collection. The Pivot-tables collection contains all the Pivot Table objects. The Pivot Table object contains methods & properties. Let us see methods and their syntax.
VBA Pivot Table Methods
Let us see about Pivot Table object Methods in Excel VBA. The following methods and their syntax we use to automate pivot tables in VBA code.
| Method | Syntax |
|---|---|
| AddDataField | PivotTable.AddDataField (Field, Caption, Function) |
| AddFields | PivotTable.AddFields (RowFields, ColumnFields, PageFields, AddToTable) |
| AllocateChanges | PivotTable.AllocateChanges |
| CalculatedFields | PivotTable.CalculatedFields |
| ChangeConnection | PivotTable.ChangeConnection (conn) |
| ChangePivotCache | PivotTable.ChangePivotCache (bstr) |
| ClearAllFilters | PivotTable.ClearAllFilters |
| ClearTable | PivotTable.ClearTable |
| CommitChanges | PivotTable.CommitChanges |
| ConvertToFormulas | PivotTable.ConvertToFormulas (ConvertFilters) |
| CreateCubeFile | PivotTable.CreateCubeFile (File, Measures, Levels, Members, Properties) |
| DiscardChanges | PivotTable.DiscardChanges |
| DrillDown | PivotTable.DrillDown (PivotItem, PivotLine) |
| DrillTo | PivotTable.DrillTo (PivotItem, CubeField, PivotLine) |
| DrillUp | PivotTable.DrillUp (PivotItem, PivotLine, LevelUniqueName) |
| GetData | PivotTable.GetData (Name) |
| GetPivotData | PivotTable.GetPivotData (DataField, Field1, Item1, Field2, Item2, Field3, Item3, Field4, Item4, Field5, Item5, Field6, Item6, Field7, Item7, Field8, Item8, Field9, Item9, Field10, Item10, Field11, Item11, Field12, Item12, Field13, Item13, Field14, Item14) |
| ListFormulas | PivotTable.ListFormulas |
| PivotCache | PivotTable.PivotCache |
| PivotFields | PivotTable.PivotFields (Index) |
| PivotSelect | PivotTable.PivotSelect (Name, Mode, UseStandardName) |
| PivotTableWizard | PivotTable.PivotTableWizard (SourceType, SourceData, TableDestination, TableName, RowGrand, ColumnGrand, SaveData, HasAutoFormat, AutoPage, Reserved, BackgroundQuery, OptimizeCache, PageFieldOrder, PageFieldWrapCount, ReadData, Connection) |
| PivotValueCell | PivotTable.PivotValueCell (RowLine, ColumnLine) |
| RefreshDataSourceValues | PivotTable.RefreshDataSourceValues |
| RefreshTable | PivotTable.RefreshTable |
| RepeatAllLabels | PivotTable.RepeatAllLabels (Repeat) |
| RowAxisLayout | PivotTable.RowAxisLayout (RowLayout) |
| ShowPages | PivotTable.ShowPages (PageField) |
| SubtotalLocation | PivotTable.SubtotalLocation (Location) |
| Update | PivotTable.Update |
More Useful Pivot Table Resource:
You can refer the following link to find more useful Pivot Table resource.
Other Useful Resources:
Click on the following links of the useful resources. These helps to learn and gain more knowledge.
VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables
VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog
