0

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:

 With Worksheets("chart_sheet")
       ActiveChart.SetSourceData Source:=Worksheets("data_sheet").Range("A1:A20")
 End With
Community
  • 1
  • 1
Eghbal
  • 3,892
  • 13
  • 51
  • 112

2 Answers2

2

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:

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

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

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.

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")
YowE3K
  • 23,852
  • 7
  • 26
  • 40
1

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

with sheets("chart_sheet")
h2so4
  • 1,559
  • 1
  • 10
  • 11
  • did you verify that chart_sheet does exist ? – h2so4 Dec 24 '16 at 15:36
  • Returned error: `Object variable or With block variable not set` in this line `ActiveChart.SetSourceData Source:=Sheets("data_sheet").Range("A1:A20")` – Eghbal Dec 24 '16 at 16:08