Pivot-chart changes chart type after applying filter

Question

I am trying to create a static format for pivot chart in MS Excel (including same colors and the same chart-type). I have created a format template and applied it to the pivot chart, but after applying filter within pivot chart, all defined colors and chart type are gone. They are set to default. I have researched and it seems that this is a typical behaviour for Pivot charts/Filters in Excel, but someone maybe knows workaround. Because in my case predefined colors and chart type are really important!

Answer 1

I have found the workaroud in Excel and VBA.

Excel steps

1. Choose one specific filter (it is best to select the default filter in the pivot diagram)
2. First set all colors for all series manually in data series options (click twice on diagram). It is important that the filling is set instead of automatic, single-color filling.
3. Set diagram type manually (right click -> change diagram type). Select the desired chart type for all data series.

VBA steps (necessary for changing diagram type!)

1. Right click on the tab where the diagram is located -> Show code
2. Add Worksheet_PivotTableUpdate subprocedure (every time the pivot table changes, including the filter, this procedure is called)
3. Activate the chart with:

    ActiveSheet.ChartObjects ("chartName"). Activate

4. Reference the name of the data series that a certain diagram type should have (e.g. line chart)

    ActiveCharts.SeriesCollection ("Series_Name"). Select
    ActiveCharts.SeriesCollection ("Series_Name"). ChartType = xLine