Filter Pivot Chart without Changing the Chart Format

[Manster]

Hi Experts,

I have a problem regarding the pivot chart when filtering using a slicer. My problem is:
The chart format will change when I filter the data, for example, "Year". The "Target" line will change to the clustered column.

Therefore, I hope anyone can help me to maintain the line of "Target" if I filtered the chart, and at the same time, other data like A, B, C, D, E will maintain the clustered columns.

Please find an attachment (Combo Pivot Chart) for your kind attention.

Thank you.

 

[p45cal]

If you're happy to have a macro, in the sheet concerned's own code-module, something along the lines of:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
On Error Resume Next
With ActiveSheet.ChartObjects(1).Chart
  .ChartType = xlColumnClustered
  .FullSeriesCollection("Target").ChartType = xlLine
End With
End Sub

 

[Manster]

If you're happy to have a macro, in the sheet concerned's own code-module, something along the lines of:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
On Error Resume Next
With ActiveSheet.ChartObjects(1).Chart
  .ChartType = xlColumnClustered
  .FullSeriesCollection("Target").ChartType = xlLine
End With
End Sub

Yes!!! Of course, I am pleased about this. You save my day, Sir! I spent around 8 hours yesterday searching on YouTube and Google. Then, I decided to ask in this great forum for help. Thank you again, Sir! May God bless you.

 

[Marilyn Ji]

If you're happy to have a macro, in the sheet concerned's own code-module, something along the lines of:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
On Error Resume Next
With ActiveSheet.ChartObjects(1).Chart
  .ChartType = xlColumnClustered
  .FullSeriesCollection("Target").ChartType = xlLine
End With
End Sub

Hi Can you send the final file you have VBA code in it. I have simulated the same code in the attached file, but it was not work, I don't know why. The Pivot chart still change when I filtered.

 

[p45cal]

Have you made sure the code's in the right place:

in the sheet concerned's own code-module

re:

the same code in the attached file

There is no attached file, so if you were to attach one I'll try and adapt the code (you probably dont have a series called Target).