Microsoft Office Tutorials and References
In Depth Information
The read-write Name property returns or sets the name of the PivotField. This is the value that
appears in the label for that field. The Value property is the same as the Name property.
The read-only SourceName property returns the name of the field in the original source data. This
may differ from the value of the Name property if the Name property has been changed.
Excel also lets us group (and ungroup) the data for a selection of pivot items into a single new
pivot item. This is done using the Group and Ungroup methods of the Range object. Note that
these methods apply to the Range object, not the PivotField or PivotItem objects.
The Group method has two syntaxes, but we will look at only the more flexible of the two. (For all
of the details on the Group method, we refer the interested reader to the Excel help documentation.)
Let us look at an example. Referring as usual to the pivot table in Figure 20-10 , the following code
selects all labels and data for Boston and New York and then groups this data into a single group.
creates both a new pivot field and a new pivot item. The pivot field is called Store City2 and
contains the existing Los Angeles pivot item along with a new pivot item, which would have been
given the name Group1 by Excel if we had not specified the name Eastern.
.PivotSelect "'Store City'[BOSTON,'New York']", xlDataAndLabel
Set rng = Selection
.PivotFields("Store City2").PivotItems(1). Name = "Eastern"
.PivotSelect "Eastern", xlDataAndLabel
Figure 20-27. Illustrating the Group method
20.7.8 Data Field Calculation
Normally, data fields show a value based on the Function property of the field. On the other hand,
Excel also permits us to change the meaning of the value that is displayed in a data field.