Refreshing Pivot Tables Resets PivotChart Colors

[MattGreer]

I have a Pivot Chart attached to a Pivot Table. I created a macro to loop through the workbook and refresh all pivot tables. I do this whenever I change some settings that change values in the source table and all the pivot-things need to update.

Yesterday, no problem, changes and refreshes proceeded along with no issues whatsoever.

Here's the code:

Private Sub Update_Pivot_Click()

Dim wks As Worksheet
Dim pvt As PivotTable
For Each wks In Worksheets
  For Each pvt In wks.PivotTables
    pvt.RefreshTable
  Next pvt
Next wks

End Sub

I saved the color scheme/type of chart as a custom type, so at least now when it resets I can go back and reapply. And I'm going to record a macro in a sec so I can add that to the code to simply reapply the template (I assume this can be coded) whenever I hit the update.

But, a few questions:

1. Why is this happening now and it wasn't yesterday? And it's only happening to one of the pivot charts.
2. Is there a way to prevent this from happening?


Thanks!!


Matt

 

[MattGreer]

Well I figured out the issue.

One, you can record a macro to apply a custom format. But after the fact it kept throwing up an error so that didn't work for me.

The reason this was happening was I created a second pivot table based off the original data and had another chart on the same page. Once I deleted the chart and the source pivot table the problem went away. Definitely an issue but I can work around this for now. The reason I did this to begin with was I wanted a few different custom charts based off the same pivot table and I couldn't hide data. Like my overall chart has data for four things, but on another chart I just wanted two of those things. Couldn't do it and I still don't know how.

Thanks!!


Matt