PivotTable Macro Reverts to Count Function
 

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.

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

Regards,

Peo Sjoblom

"LTofsrud" wrote in message
...
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.

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"LTofsrud" wrote in message
...
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.

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

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"LTofsrud" wrote in message
...
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.

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"LTofsrud" wrote in message
...
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" wrote:

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

--------------------------------------------------------------------------
--

"LTofsrud" wrote in message
...
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.