VBA to chart multiple named ranges on different tabs

0
votes

I have a chart which is updated via a VBA macro assigned to a developer button:

ActiveChart.SetSourceData Source:=Sheets("Tab1").Range("Range1")

The VBA references a named range (Range1) which uses OFFSET logic to reference the range of data needed, in the floowing way.

=OFFSET('data sheet'!$A$6,0,0, COUNTIF('data sheet'!$A$7:$A$506,">1")+1,COUNTIF('data sheet'!$A$3:$GT$3,"*"))

I now have need to add in more series to this chart and would like to add Range 2 to my chart but find that amending my VBA to the following is not working:

ActiveChart.SetSourceData Source:=Union(Sheets("Tab1").Range("Range1"),Sheets("Tab2").Range("Range2"))

ps, I have not included an example dataset here as all parts othe process use dynamic named ranges so a solution built on any nXn data grid should work here as long as variables are stored in column form

2
vbaexcel
Can you try to add more sources with macrorecorder and see what is it producing? - Vityata
Hi, the recorder doesn't use the .SetSourceData syntax. It produces: ActiveChart.SeriesCollection.NewSeries ActiveChart.FullSeriesCollection(23).Name = "='Tab2" ActiveChart.FullSeriesCollection(23).Values = _ "='Tab2'!$B$7:$B$201" - Joel B

2 Answers

1
votes
ActiveChart.SeriesCollection.Add [Range2]
0
votes

Can you try with this:

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=" & [my_range].Name

Instead of a union. [my_range] should be Range2 in your case.