[SOLVED] Pivot Table

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

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

"teresa" wrote:

> 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
>

Thanks so much for this

"sebastienm" wrote:

> 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
>
> "teresa" wrote:
>
> > 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
> >