How do I keep formatting on an Excel 2010 Pivot Chart?

Question

I have some VBA code that runs a SQL Query, dumps it into an Excel 2010 spreadsheet and refreshes the 2 Pivot Tables based off the data. I also have 2 Pivot Charts based off of the Pivot Tables, and every time the Tables get updated, the formatting on the Charts is lost.

I followed the instructions in Section 12.3 of this book which suggested creating a custom chart type based on my formatting preferences and applying it to the charts that I want to have remain formatted that way. These instructions work great if I manually modify the data then manually refresh the Pivot Table - the Charts remain properly formatted. However, when I execute this code:

For Each pt In NewSheet.PivotTables
  pt.RefreshTable
Next

the formatting is immediately lost.

Here's what the data looks like before:

and my carefully crafted pie charts:

As soon as I update the data and refresh the tables:

This is what I get:

Again, having the custom chart style applied will keep the formatting if done manually, but executing the VBA code somehow resets the custom chart type.

Answer 1

To accomplish this without the use of Pivot Charts, all you need is a dynamic Named Range and a simple (if grossly formatted) chart.

1) Create a defined Name using this formula: Series_A=OFFSET(Sheet!B1,0,0,COUNT(A:A),1), where - Sheet1!B1 refers to your first count cell in your pivot table (or any other reference, if your comfortable with OFFSET) - Count(A:A) is your column with row labels

2) Create your chart(s) with the series=Series_A (and any others you need).

As you update your pivot tables, the defined names will reference the expanding/contracting values in your row labels column and only return the values in your count column. No 0's, #N/A's, or helper columns required.