Excel 2010 Pivot Tables: Loss of table cell formatting on Refresh.

[Suuuper Geeeenius]

Hello all. I hesitate to even ask this question as I suspect (hope) it has been addressed before, however I have searched the forum tags and can't find the topic. If this is an "Everybody-knows-this-where-have-you-been?" type question, I'll gladly take any razzing if I find a solution.


**Given:**

Excel 2010, WinXP, simple pivot table, no conditional formatting, table option "Preserve cell format on update." is selected.


**Problem statement:**

Refreshing the table as well as making some other table structure changes scrambles the cell formatting; simple stuff like cell borders and shading are changed seemingly at random.


**Have Tried:**

Carefully seeking out any and all possible ways to change the table format and trying different options - Twice. Wailing, gnashing of teeth, placed ad for virgins to sacrifice.

 

[Guest]

I've encountered the issue in the past but as I don't make any changes to the pivots (other than refresh) now I didn't need to find a solution.


For other pivots that I dynamically edit using VBA, I dynamically format them as required as well.


Whilst it won't solve your issue it is a suggested workaround: Use VBA to clear all formatting & re-apply correct formatting.


Record some macro's to get the code to clear all formatting & re-apply a base style.


You'll then need to define number formats as required for the relevant pivot fields.


Don't forget Pivottable.tablerange1 to give the range of the table & Pivottable.tablerange2 to give the range of the table including the pagefield settings.


Good luck.