PivotTable Macro Reverts to Count Function

[Guest]

Hello,

I recorded a macro which creates a pivot table and when I create the layout
I make sure that it creates a sum result of the data. The initial creation
of the macro works great and I get the pivot table that I want. The problem
is that subsequence runs of the macro creates the same pivot table but
applies the Count function instead of the Sum. ?!?

I looked into the VB code behind but couldn't see anything that would result
in a different function being applied.

Has anyone else encountered this problem or have an idea as to how I might
be able to resolve this?

Thank you for your time.

 

[Peo Sjoblom]

If you have blank cells (seen as text) it will default to count when you
create the table

Regards,

Peo Sjoblom

 

[Ken Wright]

You can always add a bit of code to your own at the end that tidies it up
and ensures they all sum, something like

Dim pf As PivotField
For Each pf In ActiveSheet.PivotTables(1).DataFields
pf.Function = xlSum
Next pf

 

[Guest]

Thanks a lot for the responses guys; it was indeed the spaces! I can't
believe that I missed that.

Thanks again.

Lance

 

[Ken Wright]

Also note that spaces or text in any date fields will mean you are unable to
group that data into say Months or Quarters or years etc