The columns are referred to as fields.


The PivotFields collection contains all the fields from the data source, inclusing any calculated fields
Each pivotfield has an orientation (column, row, page or data)
The orientation can also be set from the AddFields method.

There are several field collections
RowFields -
ColumnFields -
PageFields -
DataFields -
HiddenFields -
VisibleFields -


can be added using AddField

ColumnFields -

can be added using AddField

Page Fields

can be added using AddField


cannot be added using AddField
This collection contains the "sum of" fields
These do not appear in the PivotFields collection.



AddField Method

The AddFields method can contain multiple row, column and page fields.
This method can be used to add all types of fields except Data.

objPivotTable.AddFields( RowFIelds, ColumnFields, PageFields, AddToTable) 

RowFields - can be a single name or an array of pivot field names Array("one", "two")
ColumnFields - can be a single name or an array of pivot field names Array("one", "two")
AddToTable - defaults to False which will replace all the existing fields. Change to True to add additional fields.

The AddToTable property is to specify whether to add the field(s) or to replace the existing field(s)

ActiveSheet.PivotTables(1).AddFields _ 

You can use the Array function to include more than one field in a location

ActiveSheet.PivotTables(1).AddFields _ 
     RowFields:=Array("Product","Name"), _
     ColumnFields:="State", _

You must use the orientation method to add any calculated fields to your pivot table


Set objField = ActiveSheet.PivotTables("PivotTable1").PivotFields("Customer") 

objField.Position = 1 
objField.Orientation = xlPivotFieldOrientation.xlColumnField

objField.PivotItems("Q1").Position = 2 
objField.Caption = ""

You can use the orientation and position properties to reorganise a pivot table.

ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Customer").Function = xlCount 
ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Customer").Function = xlSum
ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Customer").NumberFormat = "0"

You need to refer to the precise name of the field as it is displayed on the pivot table.

It is also possible to refer to a data field by its index number or by assigning it a different name ??


You can also change the display of individual pivot field totals

objPivotTable.PivotFields("name").Subtotals(icount) = False 

There are 12 types of total and you must turn them all off ?

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext