Macro to move chart data series reference down one row for all charts and all series in workbook.

Taskmasterbob

New Member
Joined
Feb 5, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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.

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

Attachments

  • capture example chart.PNG
    capture example chart.PNG
    14.7 KB · Views: 1
  • Capture- example.PNG
    Capture- example.PNG
    19.5 KB · Views: 1
Last edited by a moderator:

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,773
Hi Taskmasterbob and Welcome to this forum. It's literally -48C outdoors so I seem to have some time on my hands to assist you. I wasn't able to Google anything relevant, and maybe there is a much simpler resolution, but I couldn't find it. So, this is what I came up with. The code assumes that these are embedded chart in a worksheet and that the relevant sheet is the active sheet. You can adjust the code to go forward or backwards however many months you desire. It is currently coded to adjust all of your series on all of your charts on the active sheet to go forward 1 month (interval). Please save a copy of your wb before trialling the sub. HTH. Dave
Sheet code....
Code:
Function UpdateSeries(ChartNum As Integer, ChtSrs As Integer, Interval As Integer)
'increases/decreases the range of a chart series(ChtSrs) of an ...
'  embededed chart(chartnum) by any Interval specified but maintains range size
'ie. UpdateSeries(1,1,1) increases the range of Chart(1), series(1) by 1 row
'ie. UpdateSeries(1,1,-1) decreases the range of Chart(1), series(1) by 1 row
Dim TempNum1 As Integer, TempNum2 As Integer, TempNum3 As Integer, TempNum4 As Integer
Dim SplitA As Variant, SplitB As Variant, SplitC As Variant
Dim TempA As String, TempB As String, TempC As String
Dim TempStr As String, TempStr2 As String
Dim Cht As Chart, ws As Worksheet
Set ws = ActiveSheet
Set Cht = ws.ChartObjects(ChartNum).Chart
SplitA = Split(Cht.SeriesCollection(ChtSrs).Formula, ":")
SplitB = Split(Cht.SeriesCollection(ChtSrs).Formula, ",")
SplitC = Split(Cht.SeriesCollection(ChtSrs).Formula, "$")
TempNum1 = CInt(Left(SplitC(2), Len(SplitC(2)) - 1)) + Interval
TempStr = Left(SplitA(0), Len(SplitA(0)) - Len(CStr(TempNum1))) & _
                                     TempNum1 & ":$" & SplitC(3) & "$"
TempNum2 = Right(SplitB(0) & "," & SplitB(1), Len(SplitB(0) & "," & _
    SplitB(1)) - Len(Left(SplitA(0), Len(SplitA(0)) - Len(CStr(TempNum1))) & _
                 TempNum1 & ":$" & SplitC(3) & "$")) + Interval
TempStr = TempStr & CStr(TempNum2) & ","

'2nd half
TempA = SplitA(0) & ":" & SplitA(1)
TempB = SplitB(0) & "," & SplitB(1) & ","
TempC = SplitC(0) & "$" & SplitC(1) & "$" & SplitC(2) & "$" & _
                     SplitC(3) & "$" & SplitC(4) & "$" & SplitC(5) & "$"
TempStr2 = Right(TempC, Len(TempC) - Len(TempB))
TempNum3 = CInt(Right(TempA, Len(TempA) - Len(TempC))) + Interval
TempStr2 = TempStr2 & TempNum3 & ":" & SplitC(7) & "$"
TempC = TempC & SplitC(6) & "$" & SplitC(7) & "$"
TempB = TempB & "," & SplitB(2)
TempNum4 = CInt(Right(TempB, Len(TempB) - Len(TempC))) + Interval
TempStr2 = TempStr2 & CStr(TempNum4)
TempStr2 = TempStr2 & Right(Cht.SeriesCollection(ChtSrs).Formula, _
                  Len(Cht.SeriesCollection(ChtSrs).Formula) - (Len(TempB) - 1))
Cht.SeriesCollection(ChtSrs).Formula = TempStr & TempStr2
ws.Range("A" & 1).Select
End Function

Sub test()
Dim sh As Worksheet, ChrtCnt As Integer
Dim chs As ChartObject, SeriesCnt As Integer
Set sh = ActiveSheet
For ChrtCnt = 1 To sh.ChartObjects.Count
For SeriesCnt = 1 To sh.ChartObjects(ChrtCnt).Chart.SeriesCollection.Count
Call UpdateSeries(ChrtCnt, SeriesCnt, 1)
Next SeriesCnt
Next ChrtCnt
End Sub
To operate run the "Test" sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,651
Messages
5,626,089
Members
416,161
Latest member
David1966Lewis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top