‘ Set up the row fields
PT.AddFields RowFields:=”Product”, ColumnFields:=”Data”
‘ Define Calculated Fields
PT.CalculatedFields.Add Name:=”AveragePrice”, Formula:=”=Revenue/Units”
‘ Set up the data fields
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = “$#,##0,K”
End With
With PT.PivotFields(“Units”)
.Orientation = xlDataField
.Function = xlSum
.Position = 2
.NumberFormat = “#,##0”
End With
With PT.PivotFields(“AveragePrice”)
.Orientation = xlDataField
.Function = xlSum
.Position = 3
.NumberFormat = “$#,##0.00”
.Name = “Avg Price”
End With
‘ Ensure that we get zeros instead of blanks in the data area
PT.NullString = “0”
‘ Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
WSD.Activate
Range(“R1”).Select
End Sub
Calculated Items
Say that in your company one manager is responsible for Landscaping/Grounds Care and
Green Plants and Foliage Care. The idea behind a calculated item is that you can define a
new item along the Product field to calculate the total of these two items. Listing 11.5 pro-
duces the report shown in Figure 11.15.
Chapter 11 Using VBA to Create Pivot Tables
260
11
Listing 11.4 Continued
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 260
To Next Page
Loading...
Need help?
General
