VBA code to change chart type and axis for 2 series

[Taf]

I am trying to change the chart type for 2 series. Series 1 should be a Column chart with the primary axis and Series 2 should be a Line Chart with the Secondary axis. I get a 438 error. WHat am I doing wrong. Please help. I am using Excel 2016

Sub Chart1_Update()
    
    Set wsPivotChart = Sheets("Daily")
    Set pcw = wsPivotChart.ChartObjects("Chart 5")
    
    pcw.FullSeriesCollection(1).ChartType = xlColumn   '<---- This is where I get the error
    pcw.FullSeriesCollection(1).AxisGroup = XlAxisGroup.xlPrimary
    
    pcw.FullSeriesCollection(2).ChartType = xlLine
    pcw.FullSeriesCollection(2).AxisGroup = XlAxisGroup.xlSecondary

End Sub

 

[Domenic]

FullSeriesCollection is a method of the Chart object, not the ChartObject object. By the way, xlColumn is a constant, which is a member of the Constants class, and whose value is 3. Instead, you should be using xlColumnClustered, which is a member of the xlChartType class, and whose value is 51. Try the following instead...

Sub Chart1_Update()

    Set wsPivotChart = Sheets("Daily")
    Set pcw = wsPivotChart.ChartObjects("Chart 5")
 
    pcw.Chart.FullSeriesCollection(1).ChartType = xlColumnClustered
    pcw.Chart.FullSeriesCollection(1).AxisGroup = XlAxisGroup.xlPrimary
 
    pcw.Chart.FullSeriesCollection(2).ChartType = xlLine
    pcw.Chart.FullSeriesCollection(2).AxisGroup = XlAxisGroup.xlSecondary

End Sub

However, your macro can be re-written as follows...

Sub Chart1_Update()

    Dim wsPivotChart As Worksheet
    Set wsPivotChart = Sheets("Daily")
 
    Dim pcw As Chart
    Set pcw = wsPivotChart.ChartObjects("Chart 5").Chart
 
    With pcw
 
        With .FullSeriesCollection(1)
            .ChartType = xlColumnClustered
            .AxisGroup = xlPrimary
        End With
 
        With .FullSeriesCollection(2)
            .ChartType = xlLine
            .AxisGroup = xlSecondary
        End With
 
    End With

End Sub

Hope this helps!