Change Chart data range

Here a possibility
Chart1 is prepared with column "B" and each time the macro is used (Control + r) next column is selected.
If it is OK next macro will select previous column

Public WkCol As String

Sub Auto_open()
    WkCol = "B"
    UpdateChart1 (WkCol)
End Sub
 
 
Sub ChangeChart1()
    WkCol = Split(Cells(1, Cells(1, WkCol).Column + 1).Address, "$")(1)
    UpdateChart1 (WkCol)

End Sub

 

Sub UpdateChart1(NwCol As String)
Const Fom = "SERIES(Sheet1!$#$1,Sheet1!$A$2:$A$µ,Sheet1!$#$2:$#$µ,1)"
Dim LR As Long
    LR = Cells(Rows.Count, 1).End(3).Row
    
    T = Replace(Fom, "µ", LR)
    T = Replace(T, "#", NwCol)
    ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Formula = "=" & T
    ActiveSheet.Cells(1, WkCol).Select
End Sub