VBA to add data to existing plots in different tab

[javierdrm]

Hi,

First time using this forum.

I want to create a macro to add a set of data to four different plots. The data is located on a different sheet than the plots. The main thing is that I need the input of the data sheet in the macro to be dynamic, as it the plot could be populated from numerous sheets.

I have a macro to copy the template sheet, I then populate the the new sheet, after which I want a macro that plots the new to four plots located on a sheet called "Plot" (original, I know).

I hope it makes sense.

Thanks in anticipation.

 

[Worf]

Hi and welcome to the Board

We could use a user form with a RefEdit control to pick ranges, but I'm proposing a simpler method for now:

Sub Javier()
Dim ser As Series, co As ChartObject, sr, inp$, i%
inp = Application.InputBox("Example: Sheet1!d4:d9", _
"Enter new data address:", "Sheet2!h7:h12", , , , , 2)
sr = Split(inp, "!")
For i = 1 To 4                                      ' loop the charts
    Set co = Sheets("Plot").ChartObjects(i)
    Set ser = co.Chart.SeriesCollection.NewSeries   ' add series
    ser.XValues = "='Plot'!f15:f20"                 ' same for all
    ser.Values = "='" & sr(0) & "'!" & sr(1)
Next
End Sub