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

Display format for Total Column of Pivot Table
 

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