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:
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
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:
Code:
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