Macro to move series reference down one row for all charts.

Hi guys

I have a number of line charts, some with one series some with multiple. There are around 70 of these charts which plot a variable over the past year from table on another worksheet. The table has months down the columns with a value for each location of the variable. See attached. Just a table.

Each month we add another row for the current month to these tables and we need to update the graph series to include this month. Basically moving the series references down one row in the spreadsheet. Ie from Jan to jan to feb to feb. this is done manually. This is so we can have a trend of the past 12 months of the variable at each location. Across the 70 or so graphs there are around 250 series to update. As you can imagine this is a bit of an task to update each series manually and move the reference down one row in the table.

For example series values =Data!$DN$79:$DN$91 would become =Data!$DN$80:$DN$92

Is there a macro which I can use to move the reference down one row and run through all charts and series on the charts?

Thanks for the help.


Crossposted at: https://www.mrexcel.com/board/thread...kbook.1160816/

Welcome (back) to the forum.

Thanks for your question. However, we have just found out that you have posted the same question elsewhere.... and haven't told us. Feel free to cross-post on other sites...

But. Every forum has its rules. Please see Forum Rule #3 about cross-posting. So.

Do not keep it a secret. We all are willing to give you our time, freely. However, some of us get very annoyed if we waste our time developing a solution for you, when you already have a nice solution elsewhere. All we ask is that you show us some respect and tell us:

1) if you have cross-posted, and

2) the URL of the cross posts.

That way, those of us who don't want to waste their time can quickly check to see if you're already happy with another solution.

Since you are a very infrequent user of th esite, on this occasion I have done it for you. Next time, ensure that you tell us...

No problem... I am hopeless with VBA, so others will step in, for sure.