Excel Macro to create a chart

Hello,

I have recorded the Excel macro 'ChartTestMacro2' below, that creatats a chart from the Sheet "MacroTestSheet". I have been unsucessful in modifying it to work for any currently selected sheet of similar structure/format. i.e. I have been unable to find the syntax to change the line -

ActiveChart.SetSource Data Source:=Sheets("MicroTestSheet").Range(

I have tried removing "Sheets("MicroTestSheet") and the STOP . from the line however the debug then dislikes the line

Range("C:C,E:E").Select

I would appreciate some advice - I believe that I am missing something obvious

In addition how might the Range accomodate different length of data i.e. instead of (C1:C40,E1:E40) as below it could accompdate (C1:Cxx,E1,Exx).


Sub ChartTestMacro2()

Range("C:C,E:E").Select
Range("E1").Activate
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("MacroTestSheet").Range( _
"C1:C40,E1:E40"), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="MacroTestChart2"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Chart Created by Test Macro"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X Axis"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y Axis"
End With
End Sub

in anticipation, Thank to respondents.