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



## Taskmasterbob

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.


----------



## NdNoviceHlp

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


----------



## SimonKay

Hi NdNoviceHlp,

I have the same question as Taskmasterbob, but I want to move multiple charts' data in rows by 1 column to the right (dynamic 12 month view). Can you advise what I'd need to change in the above script to modify to what I need please? Any help would be appreciated.

Thanks,
Simon


----------



## NdNoviceHlp

Hi Simon and Welcome to the Board! So you have columns of data that represent months (Same amount of rows I assume) and  U want to be able to chart any 12 consecutive months and be able to step forward 1 column to the right. The above approach won't help as it relies on rows having integer values that can have 1 added to their value which is then used to update the chart series formulas. Columns have a letter value which really makes it difficult to add a value to (not impossible) which is required to update the chart series formulas. As U can see by the code, it takes some messing around to adjust the chart formulas (and it's not -48C today). You may be better off trialling an alternate VBA approach. Dave
edit: I just looked at your pics and now I am confused. Your months are in rows and your series are in columns???


----------



## NdNoviceHlp

OK Simon I think I may understand. U have only 1 chart and U want to only chart 1 column but be able to adjust the months which are rows. So start by adding all of your column series to the 1 chart. Then change the test code as follows:


		Code:
__


Sub test(SeriesNum As Integer)
Dim sh As Worksheet, ChrtCnt As Integer
Dim chs As ChartObject, SeriesCnt As Integer
Set sh = ActiveSheet
'For ChrtCnt = 1 To sh.ChartObjects.Count
ChrtCnt = 1
For SeriesCnt = 1 To sh.ChartObjects(ChrtCnt).Chart.SeriesCollection.Count
If SeriesCnt = SeriesNum Then
Call UpdateSeries(ChrtCnt, SeriesCnt, 1)
'show series
With sh.ChartObjects(ChrtCnt).Chart.SeriesCollection(SeriesCnt)
.Format.Line.Visible = True
.MarkerStyle = xlMarkerStyleAutomatic
End With
Else
' Hide Series
With sh.ChartObjects(ChrtCnt).Chart.SeriesCollection(SeriesCnt)
.Format.Line.Visible = False
.MarkerStyle = xlMarkerStyleNone
End With
End If
Next SeriesCnt
'Next ChrtCnt
End Sub

To show column 1 (one month/row lower)...


		Code:
__


Call Test(1)

To show column 2 (one month/row lower)...


		Code:
__


Call Test(2)

etc...
HTH. Dave


----------



## NdNoviceHlp

What a mess. The pics I was referring to on edit were actually from the original ingrate that never had the time to acknowledge the help. Also to call the test code, the first series is actually in column 2 but U would still use Call Test(1) etc. where (1) would indicate the series NOT the columns as posted. Anyways, sorry but I guess I'm back my original response and not really understanding your request. Dave


----------



## Jon Peltier

This is a little more straightforward:



		VBA Code:
__


Sub MoveSeriesDataDownOneRow()
  Dim chtob As ChartObject
  For Each chtob In ActiveSheet.ChartObjects
    Dim iSrs As Long, nSrs As Long
    With chtob.Chart
      nSrs = .SeriesCollection.Count
      Dim sFmla As Variant
      ReDim sFmla(1 To nSrs)
      For iSrs = 1 To nSrs
        sFmla(iSrs) = .SeriesCollection(iSrs).Formula
      Next
      For iSrs = 1 To nSrs
        Dim vFmla As Variant
        vFmla = Split(sFmla(iSrs), ",")
        Dim rXVals As Range, rYVals As Range
        Set rXVals = Range(vFmla(1))
        Set rYVals = Range(vFmla(2))
        With .SeriesCollection(iSrs)
          .XValues = rXVals.Offset(1)
          .Values = rYVals.Offset(1)
        End With
      Next
    End With
  Next
End Sub


----------



## NdNoviceHlp

Well of course that's brilliant Jon. Much simpler but yet more complex. It must not be that cold where U are  Dave
To move up one row...


		Code:
__


With .SeriesCollection(iSrs)
.XValues = rXVals.Offset(-1)
.Values = rYVals.Offset(-1)
End With


----------



## NdNoviceHlp

It occurred to me that Jon may have also resolved SimonKay's request which I'm guessing that I understand. If you want to keep the same number of rows and move the series data over 1 column, just adjust the code as follows. Dave


		Code:
__


.XValues = rXVals.Offset(0)
.Values = rYVals.Offset(0, 1)


----------



## Jon Peltier

I thought that my approach would solve a number of cases, but didn't say anything. Next time I won't keep my mouth shut.


----------



## Taskmasterbob

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.


----------



## NdNoviceHlp

I'm hoping there will be a next time. Thanks for the learning Jon. Dave


----------



## SimonKay

Firstly, Hi Dave,

Many thanks for looking at this for me. Sorry I didn't add my example.

Secondly, Hi Jon,

My thanks to you as well. Unfortunately I'm getting an error on this line:

ReDim sFmla(1 To nSrs)

Any thoughts? I do appreciate both of you looking at this and spending your time. 

Cheers,
Simon


----------



## Jon Peltier

What's the error?
Make sure sFmla and nSrs are declared properly.
Also make sure there is at least one series plotted in the chart.


----------



## Hayy

Hi guys,

I have found this topic and it has been of great help to me because it's exactly what I needed. 

I happen to have a problem with the VBA code and would like to be able to get some help. I added the macro to my excel on friday. Everything worked perfectly and I was able to shift my graphics data one row down or up. I came back on the document this morning and i got a ''Run Time Error 1004''. I understand from reading online that the problem comes from the fact that my graphics are not on the same sheet as my data. However, it worked perfectly fine 3 days ago so I do not understand why it does not work anymore.

I joined the images of the error line and where it shows up in the code

Thank you so much!


----------



## Jon Peltier

What is the SERIES formula? Usually it looks like this:



		Excel Formula:
__


=SERIES(Sheet3!$C$2,Sheet3!$B$3:$B$8,Sheet3!$C$3:$C$8,1)


However, sometimes it might look like this, without X values:



		Excel Formula:
__


=SERIES(Sheet3!$C$2,,Sheet3!$C$3:$C$8,1)


Or it might look like one of these, with X and/or Y values hard coded rather than linked to cells:



		Excel Formula:
__


=SERIES(Sheet3!$C$2,{"a";"b";"c";"d";"e";"f"},Sheet3!$C$3:$C$8,1)
=SERIES(Sheet3!$C$2,Sheet3!$B$3:$B$8,{3;6;4;7;5;8},1)


Missing or hard-coded values will cause the Range(Address) statement to fail.


----------



## btadams

I'm trying to use Jon's code but I also want to move down the Name element of the chart series. Here's my modified code:



		VBA Code:
__


Sub MoveSeriesDataDownOneRow()
  Dim chtob As ChartObject
  For Each chtob In ActiveSheet.ChartObjects
    Dim iSrs As Long, nSrs As Long
    With chtob.Chart
      nSrs = .SeriesCollection.Count
      Dim sFmla As Variant
      ReDim sFmla(1 To nSrs)
      For iSrs = 1 To nSrs
        sFmla(iSrs) = .SeriesCollection(iSrs).Formula
      Next
      For iSrs = 1 To nSrs
        Dim vFmla As Variant
        vFmla = Split(sFmla(iSrs), ",")
        Dim rXVals As Range, rYVals As Range, sName As String, rName As Range, vName As Variant
        sName = vFmla(0)
        sName = Mid(sName, Application.WorksheetFunction.Find("(", sName) + 1, Len(sName) - (Application.WorksheetFunction.Find("(", sName)))
        Set rName = Range(sName)
'       Set rXVals = Range(vFmla(1))
        Set rYVals = Range(vFmla(2))
        With .SeriesCollection(iSrs)
'            .Name = rName.Offset(1)
'            .XValues = rXVals.Offset(1)
            .Values = rYVals.Offset(1)
        End With
      Next
    End With
  Next
End Sub


The variable sName seems to have the correct string that I want to convert to a range, however when I Set rName = Range(sName), rName displays the contents of the cell when I hover over it and it puts the cell contents into the series formula when I execute .Name = rName.Offset(1).

Any ideas as to why this is happening?

Thanks in advance for any feedback!


----------



## Jon Peltier

This will apply the range to the series name, not just the value:



		VBA Code:
__


.Name = "=" & rName.Offset(1).Address(, , , True)


It's a little inconsistent, but then, it's Excel VBA.


----------

