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
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).
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("MacroTestSheet").Range( _
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="MacroTestChart2"
.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"