Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table 2007 display field only in Grand Total | Excel Discussion (Misc queries) | |||
Total Column in Pivot Table | Excel Discussion (Misc queries) | |||
Sub-Total Column in a pivot table | Excel Discussion (Misc queries) | |||
How do I display both a sum total and an average in pivot table? | Excel Worksheet Functions | |||
Pivot table question: How to display total and percent for data simultaneouly | Excel Discussion (Misc queries) |