Multiple SourceData range for charts

[biznez]

Hi
I have this problem where i am not able to set multiple data source in the chart. I am using this code below and it looks fine but not sure why its still not working. Please help. Thanks

Sub updatecht

    col="R"

    ThisWorkbook.Worksheets("Data").ChartObjects("Chart 1").Activate
    ActiveChart.SetSourceData Source:=Sheets("Data").Range("A647:A660", col & "647:" & col & "660")
end sub

The result should be
=Data!$R$647:$R$660,Data!$R$647:$R$660

but with the code the result is
=Data!$A$647:$R$660

[dflak]

You may not need VB code at all. This looks like something that could be handled with a named dynamic range. Please attach the workbook with representative but non-sensitive data. OR you can look at these two articles.
http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges
http://www.utteraccess.com/wiki/Dynamic_Charting