Microsoft Office Tutorials and References
In Depth Information
Figure 20-7. Illustrating page field order
would arrange the page fields as in Figure 20-7 . This pivot table is only for illustration of the page
field order. It was created from the original pivot table by moving the row fields to page fields.
Note also that setting PageFieldOrder to xlOverThenDown would simply reverse the
positions of Store City and Store Type.
Figure 20-7. Illustrating page field order
The following code ostensibly creates the PivotTable in Figure 20-6 at the location of the active
cell:
ActiveSheet.PivotTableWizard _
SourceType:=xlDatabase, _
SourceData:="'Source'!R1C1:R145C7", _
TableName:="Sales&Trans"
In fact, the results of executing this code are shown in Figure 20-8 . The reason nothing much
seems to have happened is that, as we mentioned earlier, the PivotTableWizard method does not
allow us to specify which fields are page, row, column, and data fields. The table in Figure 20-8 is
an empty PivotTable.
Figure 20-8. An empty PivotTable
20.4 The PivotTable Object
To understand better what must be done next, we must discuss the PivotTable object and its
various child collection objects.
Invoking the PivotTableWizard method has created a PivotTable object named Sales&Trans for us.
All PivotTable objects have a PivotFields collection, accessed through the PivotFields property.
Thus, the code:
Dim pf As PivotField
For Each pf In _
ActiveSheet.PivotTables("Sales&Trans").PivotFields
Debug.Print pf.Name
Next
produces the following list of pivot fields:
 
 
 
 
 
 
Search JabSto ::




Custom Search