Microsoft Office Tutorials and References
In Depth Information
Example 20-1. The CreatePivotFields Procedure
Year
Period
Store Code
Store City
Store Type
Transactions
Sales
Now, each PivotField object can have a designation that specifies whether this field is to be used
as a row field, a column field, a page field, or a data field. This designation is referred to as its
orientation .
It turns out that there is more than one way to set the orientation of a pivot field. One approach is
to set the pivot field's Orientation property, and another approach is to use the AddFields method.
Unfortunately, neither of these methods is sufficiently documented, so some experimentation is in
order.
As to the Orientation property approach, consider the code in Example 20-1 , which sets both the
Orientation and Position properties. We will discuss the subtleties of this code after you have
looked at it.
Example 20-1. The CreatePivotFields Procedure
Sub CreatePivotFields()
' Assume source for pivot table
' is in sheet named 'Source'
ActiveSheet.PivotTableWizard _
SourceType:=xlDatabase, _
SourceData:="'Source'!R1C1:R145C7", _
TableName:="PivotTable1"
With ActiveSheet.PivotTables("Sales&Trans")
Debug.Print "Before all:"
ShowFields
.PivotFields("Year").Orientation = xlPageField
.PivotFields("Year").Position = 1
.PivotFields("Store City").Orientation = xlRowField
.PivotFields("Store City").Position = 1
.PivotFields("Store Type").Orientation = xlRowField
.PivotFields("Store Type").Position = 2
.PivotFields("Period").Orientation = xlColumnField
Debug.Print "Before data fields:"
ShowFields
With .PivotFields("Transactions")
.Orientation = xlDataField
.Position = 1
End With
With .PivotFields("Sales")
.Orientation = xlDataField
.Position = 2
End With
 
Search JabSto ::




Custom Search