Pivot Table Problem

[Boyd L. Colglazier]

I am using the following code to create a pivot table which I want to return
the sum function for "Balance". For some reason, the pivot table started
returning Count of Balance rather than Sum of Balance so I added the line
..PivotFields("Count of Balance").Function=xlSum. This changed the count to
sum. Now the pivot table is once again returning Sum of Balance initially
and the code hangs when it trys to convert the count to sum. Is there some
code I can use to cause the function to default to sum each time the pivot
table runs?

Range(FirstCell, LastCell).Select
CurrentRegion = Selection

Set PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlDatabase, _
SourceData:=Range("A1").CurrentRegion.Address)

'Balance by AssignedTo
Set PT = PTCache.CreatePivotTable _
(TableDestination:="'AssignedTo'!R3C1", _
TableName:="PivotTable1")
With PT
.PivotFields("AssignedTo").Orientation = xlRowField
.PivotFields("Balance").Orientation = xlDataField
.PivotFields("Count of Balance").Function = xlSum
End With

 

[Debra Dalgleish]

You could change your code slightly:

'===========================
With PT.PivotFields("Balance")
.Orientation = xlDataField
.Caption = "Balance "
.Function = xlSum
End With
'============================