Repetitive charting on same graph

@Sweep

This is a very good starting point. It could be bettered if I can loop it to many sheets.
The only downside I see is that this needs a preset chart to work. If the naming of the chart could be set to the sheet name, it would make it a lot easier.
I'll try to work with it.
Thanks!

Originally Posted by sweep

Hi,

The following code could be a place to start for you?

Sub PlotCharts()
Dim iLastRow As Integer, iCount As Integer

iLastRow = Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row

    ActiveSheet.ChartObjects("Chart 1").Activate
    
For iCount = 1 To iLastRow Step 24
    
    With ActiveChart.SeriesCollection.NewSeries
        .Name = "=Sheet1!$D$" & iCount
        .XValues = "=Sheet1!$C$" & iCount + 3 & ":$C$" & iCount + 22
        .Values = "=Sheet1!$D$" & iCount + 3 & ":$D$" & iCount + 22
    End With
Next

ActiveChart.SetElement (msoElementLegendRight)

End Sub

While it's not necessarily efficient, I would delete the whole chart and start again when new data is added. That can be achieved by adding this line to the start of the code

Sheets("Sheet1").ChartObjects("Chart 1").Chart.ChartArea.ClearContents

What is the syntax I must use to run this code with ActiveSheet instead of using Sheet 1? Especially to replace "=Sheet1!$D$"
Thanks for your help?

or a slight variation creating a new chart

 Sub MakeChart()
Dim MyCell As Range, MyX As Range, MyY As Range, MyT As Range, MyS As String

Set MyCell = Range("D4") ' cell where 1st series is found
ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmooth).Select

Do While MyCell <> ""
    Set MyY = Range(MyCell, MyCell.Offset(19, 0))
    Set MyX = MyY.Offset(0, -1)
    Set MyT = MyCell.Offset(-3, 0)
    MyS = ActiveSheet.Name
    With ActiveChart.SeriesCollection.NewSeries
        .XValues = "=" & MyS & "!" & MyX.Address
        .Values = "=" & MyS & "!" & MyY.Address
        .Name = "=" & MyS & "!" & MyT.Address
    End With
    Set MyCell = MyCell.Offset(24, 0)
Loop
ActiveChart.SetElement (msoElementLegendRight)
End Sub

Originally Posted by NickyC

or a slight variation creating a new chart

 Sub MakeChart()
Dim MyCell As Range, MyX As Range, MyY As Range, MyT As Range, MyS As String

Set MyCell = Range("D4") ' cell where 1st series is found
ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmooth).Select

Do While MyCell <> ""
    Set MyY = Range(MyCell, MyCell.Offset(19, 0))
    Set MyX = MyY.Offset(0, -1)
    Set MyT = MyCell.Offset(-3, 0)
    MyS = ActiveSheet.Name
    With ActiveChart.SeriesCollection.NewSeries
        .XValues = "=" & MyS & "!" & MyX.Address
        .Values = "=" & MyS & "!" & MyY.Address
        .Name = "=" & MyS & "!" & MyT.Address
    End With
    Set MyCell = MyCell.Offset(24, 0)
Loop
ActiveChart.SetElement (msoElementLegendRight)
End Sub

This seems to be a more well organized code, logically.
However, when I try to run it, I get an error message saying "object doesn't support this property or method". I am presuming this has got to do with the type of graph selected.
When I remove the AddChart2 and make it plainly AddChart, it traces up a histogram. Is there any reference about the codes for the types of graphs? If yes, it would be helpful to have one.

Hi
your problem with my code could be a version issue. Try this variation:

Sub MakeChart()
Dim MyCell As Range, MyX As Range, MyY As Range, MyT As Range, MyS As String, ChRt As Chart

Set MyCell = Range("D4") ' cell where 1st series is found

Set ChRt = ActiveSheet.Shapes.AddChart.Chart
ChRt.ChartType = xlXYScatterLines

Do While MyCell <> ""
    Set MyY = Range(MyCell, MyCell.Offset(19, 0))
    Set MyX = MyY.Offset(0, -1)
    Set MyT = MyCell.Offset(-3, 0)
    MyS = ActiveSheet.Name
    With ChRt.SeriesCollection.NewSeries
        .XValues = "=" & MyS & "!" & MyX.Address
        .Values = "=" & MyS & "!" & MyY.Address
        .Name = "=" & MyS & "!" & MyT.Address
    End With
    Set MyCell = MyCell.Offset(24, 0)
Loop
ChRt.SetElement (msoElementLegendRight)
End Sub