Microsoft Office Tutorials and References
In Depth Information
Example 20-4. Creating a Pivot Table Using the AddFields Method
.PivotFields("Data").Position = 3
End With
End Sub
Another approach to assigning orientation for the pivot fields is to use the AddFields method of
the PivotTable object. We can use this method for all but data fields. The syntax is:
PivotTableObject .AddFields( RowFields , _
ColumnFields , PageFields , AddToTable )
The optional RowFields parameter can specify either a single pivot-field name or an array of
pivot-field names to be added as rows, and similarly for the ColumnFields and PageFields
parameters.
It is important to note that any invocation of the AddFields method will replace all existing fields
of the given type (row, column, or page) with the fields designated by the parameters of the
method. To increment rather than replace existing fields, we must set the AddToTable parameter
to True .
The alternative to CreatePivot shown in Example 20-4 uses the AddFields method for row,
column, and page fields. Note that this is shorter than the previous procedure. (It is also the
approach taken by Excel itself when we record a macro that creates this pivot table.)
Example 20-4. Creating a Pivot Table Using the AddFields Method
Sub CreatePivot2()
' Create pivot table at active cell
' Assumes that the source table is in sheet called Source
ActiveSheet.PivotTableWizard _
SourceType:=xlDatabase, _
SourceData:="'Source'!R1C1:R145C7", _
TableName:="Sales&Trans2"
ActiveSheet.PivotTables("Sales&Trans2").AddFields _
RowFields:=Array("Store City", "Store Type"), _
ColumnFields:="Period", _
PageFields:="Year"
With ActiveSheet.PivotTables("Sales&Trans2")
With .PivotFields("Transactions")
.Orientation = xlDataField
.Position = 1
End With
With .PivotFields("Sales")
.Orientation = xlDataField
.Position = 2
End With
End With
End Sub
20.5 Properties and Methods of the PivotTable Object
 
 
Search JabSto ::




Custom Search