Cant make Excel Chart to use Named Range for Series values in VBA

Hi guys,

I have simple but unsolved problem trying to make an excel chart refer to named range to pull out the values in VBA. AS you can see in the code I put MyNamed Range instead of $X$X to make it dynamic. But when I run it, it generates a run time error 1004.

ActiveSheet.Shapes.addchart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("MyNamedRange")
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).XValues = "='Sheet1'!MyNamed_Range2"

For the record MyNamedRange = Profit_generated_sheet1

Whats wrong?