How to shift chart source data xx number of rows down?

Hi all,
I'm a complete beginner to VBA, I'm building a big dashboard with repeating tables and charts. All data are located in columns AC:AO in a table of 59 rows, all charts are located in columns E:AA. When building the dashboard I always take last 59 rows and copy below, but the charts source data obviously don't update, so I'm trying to write a macro that would update the source data of a selected chart = shift the source data 59 rows down. It should be simple, I just don't know how to make a relative reference to the current source data in the chart I select. I have 4 different series of data located in different rows of the 59rows table.
This is what I currently have, but it doesn't work

Sub Change_Source()
Dim myDataRow1 As Long, myDataRow2 As Long
myDataRow1 = Range("AD10").Offset(59, 0)
myDataRow2 = Range("AO10").Offset(59, 0)
With ActiveChart
ActiveChart.SeriesCollection (1)
.Values = Range(myDataRow1, myDataRow2)
ActiveChart.SeriesCollection (2)
.Values = Range(myDataRow1, myDataRow2)
ActiveChart.SeriesCollection (3)
.Values = Range(myDataRow1, myDataRow2)
ActiveChart.SeriesCollection (4)
.Values = Range(myDataRow1, myDataRow2)
End With
End Sub

Thanks so much for an advice, it would save me HOURS of time....

hana

Hi DILIPandey,

the thing is I only need to use some of the rows in the table (4 rows for each of the charts -> 16 rows in total out of the 59 rows). I highlighted them yellow, so I need to update charts in rows 66-122 by data in table AC66-AO122. Then I take rows 64-122 and copy them in rows 123-181 and need to update the charts again - shift the source data by 59 rows down to rows 123-181 and so on and on....is there a way to do this?
With a dynamic range I need to have the same offsetting point (such as range $AE$10:$AO$10 for series 1, $AE$11:$AO$11 for series2 etc), don't I? That would mean I need to copy always the first table of data (rows 5-63) and then increase the count of rows...? still not sure now how to do it

Thanks so much for a reply!

Hana

Hi again,
I tried to write a macro using a dynamic range for each series in the chart, the range does work, but the macro doesn't, do you please have any idea what can be wrong with this macro?

Sub Change_Source()
Dim Chart4Series1 As Range
Dim Chart4Series2 As Range
Dim Chart4Series3 As Range
Dim Chart4Series4 As Range

ActiveChart.SeriesCollection(1).Values = Range("Chart4Series1")
ActiveChart.SeriesCollection(2).Values = Range("Chart4Series2")
ActiveChart.SeriesCollection(3).Values = Range("Chart4Series3")
ActiveChart.SeriesCollection(4).Values = Range("Chart4Series4")

End Sub