Thread: Pivot Chart issue

I am new to charting. I was able to kill two birds with stone for my manager. I created a pivot along with an attached pivot chart. It worked great. Then he wanted the values on each bar rotated 90 degrees and placed at the end of the each bar. I figured that out too! Then he wanted the bar colors changed. Did that one too. The only problem is that when he refreshes the pivot, the chart looses the formatting changed I made...Boooooo!

How can I preserve my formatting changes on the pivot chart?

Hi

I dont know if this will help you but i have never found a way to keep the format of a pivot chart after a refresh of the data. However when i was reformatting a pivot table i recorded it as a macro and openned VBA (Alt F11) and copyed it into the 'This Workbook' sheet (down the left hand side bar under the different sheet names). I then trimmed it down by removing the existing sub and macro name and and put it under a new heading as per below.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI _
As Boolean, Cancel As Boolean)
' Start of Macro
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"
' End of macro
End Sub


So when ever i save my workbook it will reformat the pivot charts.
An easier way might be to assign your recorded macro to a button called 'Update' so you wont need to trim it down and can just click the button to update. You can make a button with just a normal auto shape and right click to assign macro )

Hope it helps

I did something similar. I guess this has been an issue for some time....you'd think the brain power at MS would have fixed it by now.

This might help,

=> Format the chart as you'd like
=> Right Click the chart;
=> Select Chart Type
=> Select Custom Types tab
=> Select Radial button "User-defined"
=> Click Add
=> Name it and save it.

You should be able to refresh at will.

You can also then use the custom type for other charts (you'll likely need to make minor changes, however)