Solved: Make chart where source data range is variable

[annemarie]

Hi there again. I am posting again to ask for you help. I have recorded the below code but what I want to do is to make the source sheet and the data source variable. And also I want the Chart Title to be equal to the work sheet name.



Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("cont_dots_PN_color").Range( _
"N1:Y344"), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="cont_dots_PN_color"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Dots Plain Normal (Color)"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Temperature"
End With

Thank you very much for the help

annemarie

[mdmackillop]

Something like this?

Sub Chart()
Dim MySht As Worksheet
Dim MyRng As Range

Set MySht = ActiveSheet
Set MyRng = MySht.Range("A1:C15")

Call MakeChart(MySht, MyRng)
End Sub

Sub MakeChart(sh As Worksheet, rng As Range)
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=rng, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:=sh.Name
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = sh.Name
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Temperature"
End With
End Sub

[annemarie]

Looks great! Thanks..