使用图表表中的SetSourceData更新图表

问题描述：

我在工作表"Daten_G9-G10"中有一个嵌入式图表.在同一工作表中，有一个名称"Chart9"，它是一个动态范围:

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))

=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

到目前为止，此宏运行良好.但是我将图表移到了新的表格中:"G9".那就是我的问题开始的地方:运行时错误'438'，对象不支持此属性或方法".

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".

我该如何调整可以更新此图表的宏，同时仍将工作表"Daten_G9-G10"中的名称称为图表的数据范围?

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?

答

现在，您的代码仅引用 ActiveSheet ，这可能会导致错误，因为您的图表位于ActiveSheet上，但是数据在另一张纸上.

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.

尝试一下:

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

根据评论更新

如果要处理图表，请修改以使用 Sheets 集合( Worksheets 会引发错误)

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

使用图表表中的SetSourceData更新图表

相关推荐