VBA/ Macro for Formatting Pivot Chart - *Not even a Rookie yet*

[MsCait]

Element Type: Pivot Chart

Issue: Slicers reformat Secondary Axis

Attempted Solution: Below Macro, works up to a point

Issue with Solution: When the "FullSeriesCollection" Expands or contracts error occurs- happens with new data or when using slicer

Sub Change_ETF()
'
' Change_ETF Macro
'

'
ActiveSheet.ChartObjects("ETF LOAD FACTORS").Activate
ActiveChart.ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(1).AxisGroup = 1
ActiveChart.FullSeriesCollection(2).ChartType = xlLineMarkers
ActiveChart.FullSeriesCollection(2).AxisGroup = 2
ActiveChart.FullSeriesCollection(2).Format.Line.Visible = msoFalse
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
End With
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Solid
End With

ActiveChart.FullSeriesCollection(3).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(3).AxisGroup = 1
ActiveChart.FullSeriesCollection(4).ChartType = xlLineMarkers
ActiveChart.FullSeriesCollection(4).AxisGroup = 2
ActiveChart.FullSeriesCollection(4).Format.Line.Visible = msoFalse
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
End With
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Solid
End With
ActiveChart.FullSeriesCollection(5).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(5).AxisGroup = 1
ActiveChart.FullSeriesCollection(6).ChartType = xlLineMarkers
ActiveChart.FullSeriesCollection(6).AxisGroup = 2
ActiveChart.FullSeriesCollection(6).Format.Line.Visible = msoFalse
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
End With
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Solid
End With
ActiveChart.FullSeriesCollection(7).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(7).AxisGroup = 1
ActiveChart.FullSeriesCollection(8).ChartType = xlLineMarkers
ActiveChart.FullSeriesCollection(8).AxisGroup = 2
ActiveChart.FullSeriesCollection(8).Format.Line.Visible = msoFalse
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
End With
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Solid
End With
ActiveChart.FullSeriesCollection(9).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(9).AxisGroup = 1
ActiveChart.FullSeriesCollection(10).ChartType = xlLineMarkers
ActiveChart.FullSeriesCollection(10).AxisGroup = 2
ActiveChart.FullSeriesCollection(10).Format.Line.Visible = msoFalse
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
End With
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Solid
End With



End Sub

Mess of a Macro (what I wish I could do, but I don't understand):

Sub Change_ETF()
'
' Change_ETF Macro
'

'
ActiveSheet.ChartObjects("ETF LOAD FACTORS").Activate

ActiveChart.ChartType = xlColumnClustered

If ActiveChart.FullSeriesCollection = "Load Factor" Then
ActiveChart.FullSeriesCollection.ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection.AxisGroup = 1
End If

If ActiveChart.FullSeriesCollection = "No. of Buses" Then
ActiveChart.FullSeriesCollection.ChartType = xlLineMarkers
ActiveChart.FullSeriesCollection.AxisGroup = 2
End If

End Sub