Default Number Format
To change the number format in the user interface, double-click the Sum of Revenue title, click the Number button, and set an appropriate number format.
When you have large numbers, it helps to have the thousands separator displayed. To set this up in VBA code, use the following:
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. If you are working for a relatively young computer company where everyone uses "K" for the thousands separator, you're in luck because Microsoft makes it easy to use the K abbreviation. However, if you work at a 100+ year-old soap company where you use "M" for thousands and "MM" for millions, you have a few more hurdles to jump. 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,,""M"""
In case it is hard to read, the format is quote, pound, comma, pound, pound, zero, period, zero, comma, comma, quote, quote, M, quote, quote, quote. The three quotes at the end are correct. Use two quotes to simulate typing one quote in the custom number format box and a final quote to close the string in VBA.