When you have large numbers, it helps to have the thousands separator displayed. To set this up in VBA code, use

PT.PivotFields("Sum of Revenue").NumberFormat = "#,##0"

Some companies often have customers who typically buy thousands or millions of dollars of goods. You can display numbers in thousands by using a single comma after the number format. Of course, you will want to include a "K" abbreviation to indicate that the numbers are in thousands:

PT.PivotFields("Sum of Revenue").NumberFormat = "#,##0,K"

Of course, local custom dictates the thousands abbreviation. I grew up in a young computer company where everyone was computer literate and we used "K" for the thousands separator. My fellow accountants at the 100+ year old soap company down the street always needed to use "M" for thousands and "MM" for millions. Microsoft is clearly a young computer company and makes it easy to use the K abbreviation, but hard to use the M abbreviation. You are required to prefix the M character with a backslash to have it work: PT.PivotFields("Sum of Revenue").NumberFormat = "#,##0,\M"

Alternatively, you can surround the M character with a double quote. To put a double quote inside a quoted string in VBA, you must put two sequential quotes. To set up a format in tenths of millions that uses the #,##0.0,,"MM" format, you would use this line of code: PT.PivotFields("Sum of Revenue").NumberFormat = "#,##0.0,,""MM

In case it is hard to read, the format is Quote, Pound, Comma, Pound, Pound, Zero, Period, Zero, Comma, Comma, Quote, Quote, M, M, Quote, Quote, Quote. The three

0 0

Post a comment