Syntax error in chart macro

[artz]

Hi,

I am trying to write a simple macro to create a chart over the used range in a worksheet. The first part of the macro correctly selects the used range which in this case is A1 to F19 when I single step through the macro. A listing of the macro is shown below:

Sub AddChartObject()
'
Sheets("Sheet1").Activate
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
 
LastRow = Cells(65536, 1).End(xlUp).Row
LastCol = Cells(1, 255).End(xlToLeft).Column
MyArea = "='" & ActiveSheet.Name & "'!R1C1:R" & LastRow & "C" & LastCol
    With ActiveSheet.ChartObjects.Add _
            (Left:=100, Width:=375, Top:=75, Height:=225)
             .Chart.ChartType = xlLine
       '.Chart.SetSourceData Source:=Sheets("Sheet1").Range("A1:F19")
       .Chart.SetSourceData Source:="='" & ActiveSheet.Name & "'!R1C1:R" & LastRow & "C" & LastCol
    End With
End Sub

When I place the formula given in MyArea as an argument for the source data, I receive a syntax error, so obviously it is the wrong argument. Numerous attempts to correct it also failed. As a sanity check, I placed the used range in an argument for the source data and was able to create the expected chart.

Any suggestion to fix the syntax error in the macro would greatly be appreciated.

Thanks,

Art

 

[p45cal]

I think the argument needs to be a range, not a string?

 

[artz]

P45cal,

Thanks for your reply. I didn't realize that the argument was a text string. Do you know how it can be changed to range?

Thanks,

Art

 

[p45cal]

Well, since you've gone to the trouble of selecting stuff, perhaps

.Chart.SetSourceData Source:=Selection

(untested).

 

[artz]

pc45cal,

Thanks! You nailed it. I am surprised it was so simple, but for me not obvious.

Good call.

Thanks again,

Art