Using functions in Pivot Tables

[cfcanter]

I am trying to create a pivot table using xl 2002. The following code words using the macro recorder:

With ActiveSheet.PivotTables("PivotTable2&quot.PivotFields("STD COST&quot
.Orientation = xlDataField
.Caption = "Max of STD COST"
.Function = xlMax
End With

However, the code is cumbersome and pivot tables can't do the calculated fields I need.

This code should do the same thing but doesn't work:

'this creates the pivot table
Set ptcache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Range("a1&quot.CurrentRegion.Address)
'
'create the pivot table from the cache
'
Set pt = ptcache.CreatePivotTable(Sheets("bypart&quot.Range("a1&quot, "ByPartNumber&quot
With pt
'add row fields
.PivotFields("Part Number&quot.Orientation = xlRowField
.PivotFields("Description&quot.Orientation = xlRowField
' add calculated fields
.CalculatedFields.Add "Sales $ per Unit", "=('Total Sales'/'Sales Qty')"
.CalculatedFields.Add "Total Std Cost", "=('Std Cost' * 'Sales Qty')"
.CalculatedFields.Add "Total GM $", "=('Total Sales' - 'Total Std Cost')"
.CalculatedFields.Add "GM $ per Unit", "=('Sales $ per Unit' - 'Std Cost')"
' places result data in pivot tables
.PivotFields("Sales Qty&quot.Orientation = xlDataField
.PivotFields("Total Sales&quot.Orientation = xlDataField
.PivotFields("Sales $ per Unit&quot.Orientation = xlDataField
.PivotFields("Total Std Cost&quot.Orientation = xlDataField
.PivotFields("Std Cost&quot.Orientation = xlDataField
.PivotFields("Std Cost&quot.Caption = "STD COST"

' fails here
.PivotFields("Std Cost&quot.Function = xlMax

Why isn't the function working? How can I fix it?

Craig Canter
Cost Accountant
Sipex
Milpitas, CA