PivotChart will not preserve series formatting for some charts when reopening workbook

[keeena]

I have a WB with 4 PivotCharts. When I close and reopen the WB, 2 of
my PivotCharts have slightly skewed formatting. Can somone help
determine why?

Details:
PTable 1 / PChart 1, PTable 2 / PChart 2 all based on static data in
Sheet 1.
PTable 3 / PChart 3, PTable 4 / PChart 4 all based on static data in
Sheet 2.
All PTables were created from a new PCache object. I'm using any
single data cache for more than 1 PTable.
When I reopen, PChart 1 and PChart 3 have the following problem:
- Series order goes back to the default order.
- Series colors remains in the old order.


1. I know that changing the underlying PivotTable will reset the
corresponding PivotChart's series formatting. However, I am not making
any PT changes. Just close and reopen the WB.

2. The PTable has PreserveFormatting enabled and Refresh Data is
disabled.

3. There are no volatile operations that would cause the PTable to
change (e.g. Date() function)

4. I know I could write code to re-set the formatting upon opening the
WB. I would rather find the reason why some PCharts's are changing
upon reopening. I'd be OK if either all of them changed (I'd just
curse Excel) or none of them change (really how it should be working as
far as I understand the known quirks with pivots).

TIA,
-K

 

[keeena]

Problem resolved.

I didn't know that PivotChart series order was strictly tied to the
underlying PivotTable. Setting the SeriesOrder in the PivotChart will
work while the WB is open, but on next open, the chart reverts back to
the order as it exists in the PivotTable. I guess this is by design -
I've never noticed this behavior before.

Solution is to change series order at the PivotTable. This will bubble
up to the PivotChart and will retain after close/reopen.

A nice enhancement would be to automatically order the PivotTable when
its respective PivotChart series order is changed. Or at least prevent
PivotChart series modifications so its more obvious.

Hope this helps someone else out there.