Pivot Table

[Guest]

Hi,

Im recording a pivot table macro, with the aim of playing this macro for a
set of data in a given format.

My prooblem when recording is that when I am at Layout>> Data,
and I drag the second field (TC) into Data,
sometimes it turns up as Sum Of TC
and sometimes as Count of TC

How do I ensure that its always Sum Of TC

Thanks

 

[Guest]

Hi,
At creation, you could do:

With ActiveSheet.PivotTables("PivotTable1")
'.....
'Bellow , adds 2 fields to the pivot, and then move one to Data as a
Sum
.AddFields RowFields:="Field1", ColumnFields:="TC"
With .PivotFields("TC")
.Orientation = xlDataField
.Caption = "Sum of TC"
.Function = xlSum '<-------------
End With
End With

or

Dim pf as Pivotfield
On error resume next
Set pf= ActiveSheet.PivotTables("PivotTable2").PivotFields("Count of TC")
If err=0 then 'case where the field is found; change to Sum
pf.Function =xlSum
End if
On error goto 0

Regards,
Sebastien

 

[Guest]

Thanks so much for this

Hi,
At creation, you could do:

With ActiveSheet.PivotTables("PivotTable1")
'.....
'Bellow , adds 2 fields to the pivot, and then move one to Data as a
Sum
.AddFields RowFields:="Field1", ColumnFields:="TC"
With .PivotFields("TC")
.Orientation = xlDataField
.Caption = "Sum of TC"
.Function = xlSum '<-------------
End With
End With

or

Dim pf as Pivotfield
On error resume next
Set pf= ActiveSheet.PivotTables("PivotTable2").PivotFields("Count of TC")
If err=0 then 'case where the field is found; change to Sum
pf.Function =xlSum
End if
On error goto 0

Regards,
Sebastien