Update chart with SetSourceData in a chart sheet

Question

I have an embedded chart in the sheet "Daten_G9-G10". In the same sheet there's the name "Chart9", which is a dynamic range:

=OFFSET('Daten_G9-G10'!$G$31;0;0;MAX('Daten_G9-G10'!$F$31:$F$51)+1;COUNTA('Daten_G9-G10'!$G$30:$AAA$30))

My goal is to update the chart's data range.

Sub UpdateChartSourceData()
  With ActiveSheet
   .ChartObjects(1).Chart.SetSourceData _
   Source:=.Range("Chart9"), _
   PlotBy:=xlRows
  End With
End Sub

This macro worked well so far. But I've moved the chart into a new sheet: "G9". That's where my problem begins: "Runtime error '438', Object doesn't support this property or method".

How do I have to adjust the macro that I can update this chart, still referring to the name in the sheet "Daten_G9-G10" as the chart's data range?

Answer 1

RIght now, your code only refers to ActiveSheet, which is causing an error probably because your chart is on the ActiveSheet, but the data is on another sheet.

Try this:

Sub UpdateChartSourceData()
  ' ## Modify the next line to refer to your chart sheet's name:
  With Worksheets("_Sheet_With_Chart_")
      .ChartObjects(1).Chart.SetSourceData _
          Source:=Worksheets("Daten_G9-G10").Range("Chart9"), _
          PlotBy:=xlRows
  End With
End Sub

Update from comments

If dealing with a Chart Sheet, modify to use the Sheets collection (Worksheets will raise an error)

Sub UpdateChartSourceData()
  ' ## Modify the next line to refer to your chart sheet's name:
  With Sheets("_Sheet_With_Chart_")
          .Chart.SetSourceData Source:=Worksheets("Daten_G9-G10").Range("Chart9"), _
          PlotBy:=xlRows
  End With
End Sub