title: VBA to chart multiple named ranges on different tabs
body: <p>I have a chart which is updated via a VBA macro assigned to a developer button:</p>
  
  <pre><code>ActiveChart.SetSourceData Source:=Sheets("Tab1").Range("Range1")
  </code></pre>
  
  <p>The VBA references a named range (Range1) which uses OFFSET logic to reference the range of data needed, in the floowing way.</p>
  
  <pre><code>=OFFSET('data sheet'!$A$6,0,0, COUNTIF('data sheet'!$A$7:$A$506,"&gt;1")+1,COUNTIF('data sheet'!$A$3:$GT$3,"*"))
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>ActiveChart.SetSourceData Source:=Union(Sheets("Tab1").Range("Range1"),Sheets("Tab2").Range("Range2"))
  </code></pre>
  
  <p>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</p>