Updating my PivotChart removes the formatting of the chart.

[Ship_m]

My problem is that when I updating my PivotChart it removes the formatting of the chart.

I found the following explanation in MS kb (KB215904) se below.

Dos anybody have a better workaround to this problem?

I have approximately 35 PivotTable/ PivotChart in the workbook that needs updating (and subsequently reformatting) so the workaround mentioned below is not really an option.

MS KB215904
SYMPTOMS
When you perform any of the following pivot operations in your PivotChart or the underlying PivotTable report, one or more of your data series lose their formatting:
• You refresh the PivotChart or the underlying PivotTable.
• You add, remove, or move any fields that you have added to your PivotChart or PivotTable.
• You show or hide individual items in any PivotChart or PivotTable field.

CAUSE
When you perform a pivot operation in a PivotChart or the underlying PivotTable report, Microsoft Excel removes, and then adds the data series back to the chart. When this occurs, all formatting that you have applied to the data series in the chart is lost.

WORKAROUND
To work around this problem, you can record a Visual Basic for Applications macro that saves the changes that you make to your PivotChart. Then, you can run the macro to reapply the formatting as necessary.

 

[Andrew Poulsom]

Currently that's the only workaround. The problem is fixed in Excel 2007:

http://blogs.msdn.com/excel/archive/2006/04/19/579649.aspx

 

[Ship_m]

Hi Andrew

Thank you for your posting, thou not the answer I was looking for but at least I can stop searching high and low for a fix.
Great link.

Have nice day
[/img]