I would like to copy a chart that already exists and change the data range to
show a different set of data. I used the record macro tool and it shows me
what I basically want to do. The problem is I need to change the "R" value so
I think I need to use a variable of some sort but the ways I have been doing
it doesn't work. Here is the sample I recorded:

Sub test()
'
' test Macro
'

'
Sheets("216-3").Select
Sheets("216-3").Copy Befo=Sheets(1)
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = "=Data!R6735C4:R6786C4"
ActiveChart.SeriesCollection(2).Values = "=Data!R6735C5:R6786C5"
ActiveChart.SeriesCollection(3).Values = "=Data!R6735C9:R6786C9"
ActiveChart.SeriesCollection(4).Values = "=Data!R6735C11:R6786C11"

End Sub

I am going to put this in a loop to create mulitple charts but I need to
change the R6735 to R6818 as an example but as I mentioned, I am having a
problem with making a variable work in this situation. If anyone has a
suggestion I would appreciate it. Thank you in advance.