使用VBA（EXCEL）从不同工作表中选择图表的数据范围

How can I select data range for a chart from different sheet using VBA? Suppose that data sheet name is data_sheet, chart sheet name is chart_sheet, and my data range is A1:A20. How can I do this in excel? I checked THIS but didn't work for different sheets. Otherwise, I checked THIS but returned this error: Subscript out of range:

如何使用VBA从不同的工作表中选择图表的数据范围?假设数据表名称是data_sheet,图表表名称是chart_sheet,我的数据范围是A1:A20。我怎样才能在excel中做到这一点?我检查了这个,但不适用于不同的床单。否则,我检查了这个,但返回了这个错误:下标超出范围:

 With Worksheets("chart_sheet")
       ActiveChart.SetSourceData Source:=Worksheets("data_sheet").Range("A1:A20")
 End With

2 个解决方案

#1

Assuming "chart_sheet" is the name of your Chart and "data_sheet" is the name of your Worksheet, I think you want to do the following:

假设“chart_sheet”是您的图表的名称,“data_sheet”是您的工作表的名称,我想您想要执行以下操作:

Charts("chart_sheet").SetSourceData Source:=Worksheets("data_sheet").Range("A1:A20")

Your With block was not doing anything useful - the purpose of a With block is to allow you to just type . as a shortcut for something like Worksheets("data_sheet")..

你的With块没有做任何有用的事情 - With块的目的是允许你输入。作为工作表(“data_sheet”)之类的快捷方式..

So something like:

所以类似于:

With Sheets("chart_sheet")
    .SetSourceData Source:=Worksheets("data_sheet").Range("A1:A20")
End With

would work, because the .SetSourceData is an abbreviation of Sheets("chart_sheet").SetSourceData. (Notice also that the Sheets collection contains both Worksheets and Charts objects, so Charts("chart_sheet") and Sheets("chart_sheet") both point to the same thing.)

可以工作,因为.SetSourceData是Sheets(“chart_sheet”)的缩写.SetSourceData。 (另请注意,Sheets集合包含工作表和Charts对象,因此Charts(“chart_sheet”)和Sheets(“chart_sheet”)都指向同一个东西。)

ActiveChart refers to the currently active chart, just as ActiveSheet returns to the currently sheet. If you don't have a chart active when that piece of code executes, you will get an error.

ActiveChart引用当前活动的图表,就像ActiveSheet返回当前工作表一样。如果在执行该代码时没有激活图表,则会出现错误。

So the following piece of code would also probably have worked for you:

所以下面的代码也可能对你有用:

    Sheets("chart_sheet").Activate
    ActiveChart.SetSourceData Source:=Worksheets("data_sheet").Range("A1:A20")

#2

as chart_sheet is probably not a worksheet, did you try this ?

因为chart_sheet可能不是工作表,你试试这个吗?

with sheets("chart_sheet")

#1