To Rounding off numbers in Pivot Table Data area using Macro

Hi,

Thanks for all your valuable inputs so far.

I would like to rounding off the number in the Data area of the pivot table which is otherwise coming in "**.**" format. As for example if "24.89" is the actual number, it should show up as "25". Any help?

Sancy

Hey,

I think that can be done quite easily. Just set the number format of the pivot table field to showing 0 decimals instead of 2. In excel 2003 that can be done by right clicking the pivot table field --> field settings --> number --> select number format --> decimal places: 0.

Hi rhcpgergo,

I want it in macro. I've written some code where I would like it to fit, so that everytime the report gets generated through pivot should have teh Numbering format to "0" as you said. For that I've written a code which is though not working. The code is running without throwing any error, but it has no impact on the result. You can see below teh code that I've written:

<code>
'This command changes the formatting of any field that appears in the Values area
For Each Pf In PT.DataFields
Pf.Function = xlSum
Pf.NumberFormat = "0"

Next Pf
</code>

I think its PivotFields instead of DataFields.