Pivot Table Sum does not work

Posted by Mr Confused on November 06, 2001 1:34 AM

I am trying to build an automated pivot table which sums values. I recorded the creation of the pivot table which worked fine and summed the values as required. However, when I run the macro the values are not summed but counted (which is useless for me). What is going on? Has anyone else had this problem?
I've tried formatting the values to ensure they are numbers and don't default to strings - but still no joy.
I don't understand why it should work properly when I try this using the longhand method, but fails to work when I macro-ize it.
Am I missing some property of the pivot table?

Thanks in advance,
Mark.

'build the pivot
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"Arrays!R1C1:R1" & RowCount & "C2", TableDestination:="", TableName:="PivotTable2"
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Name", _
ColumnFields:="FTE"
ActiveSheet.PivotTables("PivotTable2").PivotFields("FTE").Orientation = _
xlDataField

Posted by Mark W. on November 06, 2001 6:08 AM

PivotTable Sum works just fine... it's your VBA code!

I'm betting that the column that you're wanting
to sum contains text data or empty cells. When
you drag such a field to the DATA area of a
PivotTable it will default to a Count summary
because it's the "best fit" for your data.

You need to explictly code for the Sum function
in your code as shown below...

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Field2")
.Orientation = xlDataField
.Name = "Sum of Field2"
.Function = xlSum

Posted by Mr Confused on November 07, 2001 3:56 AM

Re: PivotTable Sum works just fine... it's your VBA code!

Thanks very much - it works fine now. Can you tell me where I can find out more about the properties of the pivottable/pivotfield. For instance, I thought I had to force the choice but couldn't find any info on Function=xlsum. Did you find this in a good book - or is it included somewhere in the excel vba help?

Thanks again!

Posted by Mark W. on November 07, 2001 5:53 AM

Re: PivotTable Sum works just fine... it's your VBA code!

I just recorded a macro while creating a PivotTable
and looked at the results... I guess you can say
that I reversed engineered it! Thanks very much - it works fine now. Can you tell me where I can find out more about the properties of the pivottable/pivotfield. For instance, I thought I had to force the choice but couldn't find any info on Function=xlsum. Did you find this in a good book - or is it included somewhere in the excel vba help? Thanks again!