I would like to write a macro to plot a series of x & y values based on the row of the currently active cell. The first step is to click on a cell whose row you wish to plot. Then you run the macro...
Here's what I have so far :
Sub Macro44()
'
' Macro44 Macro

'
    Charts.Add
    ActiveChart.ChartType = xlXYScatterSmooth
    ActiveChart.SetSourceData Source:=Sheets("Data").Range("P5:W5"), PlotBy:= xlRows
    ActiveChart.SeriesCollection(1).XValues = _
        "=(Data!RC16,Data!RC18,Data!RC20,Data!RC22)"
    ActiveChart.SeriesCollection(1).Values = _
        "=(Data!RC17,Data!RC19,Data!RC21,Data!RC23)"
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Data"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Test"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y"
    End With
    ActiveChart.HasLegend = False
    ActiveWindow.Visible = False
End Sub
My understanding is that, for example, RC17 means the current (active) row & column 17. Instead what I plot out are the data (x & Y) of row 1.
How can I be sure that the active cell is being accurately captured & that I will be plotting the data of the active row in the chart ?

Much thanks !

ak