Set to False to
enable setting some fields as server-based
page fields before the data is read.
DefaultVersion N Variant The default version of the PivotTable report.
The code then selects the new sheet and the starting range location for the PivotTable.
Sheets("Sheet4").Select
Cells(3, 1).Select
We added two text fields (State and Products) to the PivotTable Field List pane and one
data field containing the item quantities (Qty):
With ActiveSheet.PivotTables("PivotTable1").PivotFields("State")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( ??
"PivotTable1").PivotFields("Qty"), "Sum of Qty", xlSum
This is where the code is telling the PivotTable how to display the data assigned to each
PivotField object. The Orientation property is set to a value of the xlPivotFieldOrientation
enumeration type, as shown in Table 6-3.
Table 6-3. xlPivotFieldOrientation Enumerations
Name Value Description
xlRowField 1 Row
xlColumnField 2 Column
xlPageField 3 Page
xlDataField 4 Data
xlHidden 0 Hidden
CHAPTER 6 n PIVOTTABLES 231
The Position property notes where in the row or column hierarchy the field belongs, and
therefore how the data will be grouped on the PivotTable. After we added the City field to the
Row Labels list in the PivotTable Field List pane, the next bit of code was added:
With ActiveSheet.
Pages:
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225