Display format for Total Column of Pivot Table

Hi Folks

I created an Excel Pivot table. Programatically I set the format for the
fields in "ColumnFields" section (see code below).

'=====================================================
'-- Formatting the look-n-feel of the Numbers displayed
'===================================================== Dim pvtField
For Each pvtField In pvtTable.ColumnFields '.DataFields
Select Case UCase(pvtField)
'===========================================
'Format USD with commas and 3 decimal places
'===========================================
Case "FY"
pvtTable.PivotSelect pvtField, xlDataOnly
Selection.NumberFormat = "0.000_);(0.000)"
Case "FM"
pvtTable.PivotSelect pvtField, xlDataOnly
Selection.NumberFormat = "0.000_);(0.000)"
End Select
Next pvtField

What I'm trying to do
=============
Display negative numbers in parenthesis


Question
======
The code works fine, it applies the required format , but it does not apply
the format to the TOTAL column. Is there something I'm missing in my code?

Thanks
PP

Hi,
I recorded a quick macro to format the last Grand Total column; i ran the
code on another pivot and it worked fine.
ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Row Grand Total'", _
xlDataOnly
Selection.NumberFormat = "#,##0_);(#,##0)"

i hope this helps,
Regards,
Sébastien


"PP" wrote:

> Hi Folks
>
> I created an Excel Pivot table. Programatically I set the format for the
> fields in "ColumnFields" section (see code below).
>
> '=====================================================
> '-- Formatting the look-n-feel of the Numbers displayed
> '===================================================== Dim pvtField
> For Each pvtField In pvtTable.ColumnFields '.DataFields
> Select Case UCase(pvtField)
> '===========================================
> 'Format USD with commas and 3 decimal places
> '===========================================
> Case "FY"
> pvtTable.PivotSelect pvtField, xlDataOnly
> Selection.NumberFormat = "0.000_);(0.000)"
> Case "FM"
> pvtTable.PivotSelect pvtField, xlDataOnly
> Selection.NumberFormat = "0.000_);(0.000)"
> End Select
> Next pvtField
>
> What I'm trying to do
> =============
> Display negative numbers in parenthesis
>
>
> Question
> ======
> The code works fine, it applies the required format , but it does not apply
> the format to the TOTAL column. Is there something I'm missing in my code?
>
> Thanks
> PP