Microsoft Office Tutorials and References
In Depth Information
20.4.1 Naming Data Fields
20.4.1 Naming Data Fields
We should make a few remarks about naming data fields. It is important to note that if the name of
a data field has not been changed but we make a change to the aggregate function, say from Sum
to Average , then Excel will automatically rename the data field, in this case from Sum of Sales
to Average of Sales However, once we set a new name for the data field, Excel will not .
rename it when we change the aggregate function.
We can rename a data field simply by setting its Name property. However, even though Data
fields do not seem to belong to the PivotFields collection, we cannot use the name of a pivot field
for a data field. For instance, we cannot rename Sum of Transactions to Transactions ,
since this is already taken by the pivot field. (Trying to do so will produce an error.) Thus, in
designing the source table for the pivot table, we should choose a column heading that we do not
want to use in the pivot table!
20.4.2 The Complete Code
For reference, let us put together the code required to create the pivot table in Figure 20-6 ; it is
shown in Example 20-3 .
Example 20-3. The CreatePivot Procedure
Sub CreatePivot()
' Create pivot table at active cell
' Assumes that the source table is in sheet called Source
ActiveSheet.PivotTableWizard _
SourceType:=xlDatabase, _
SourceData:="'Company Both'!R1C1:R145C7", _
' Assign field orientations and data fields
With ActiveSheet.PivotTables("Sales&Trans")
.PivotFields("Year").Orientation = xlPageField
.PivotFields("Year").Position = 1
.PivotFields("Store City").Orientation = _
.PivotFields("Store City").Position = 1
.PivotFields("Store Type").Orientation = _
.PivotFields("Store Type").Position = 2
.PivotFields("Period").Orientation = _
With .PivotFields("Transactions")
.Orientation = xlDataField
.Position = 1
End With
With .PivotFields("Sales")
.Orientation = xlDataField
.Position = 2
End With
.PivotFields("Data").Orientation = xlRowField
Search JabSto ::

Custom Search