Fields

The columns are referred to as fields.


PivotFields

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 -


RowFields

can eb added using AddField




ColumnFields -

can be added using AddField




Page Fields

can be added using AddField




DataFields

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



HiddenFields




VisibleFields



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 _ 
              RowFields:="Name"
              AddToTable:=True

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", _
     PageFields:="Date")

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

ActiveSheet.PivotTables(1).PivotFields("Date").Orientation:=xlPageField 



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 ??



Subtotals

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 ?



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