# Change Series Formula – Improved Routines

Ever make a copy of a chart, and all you want is do is change the worksheet containing the source data? Or change the X values of all series from column A to column B? It sure is a pain to edit each series’ source data range of series formula one by one.

The series formula is a simple text string, but there’s no Search and Replace feature in Excel that can access these formulas. But you can use some very simple VBA code to make wholesale changes to chart series formulas. Or you can skip all of the noise, scroll to the end of this article, and download the new Change Series Formula Utility.

## About the Chart Series Formula

A more detailed description of a chart and its series formula is presented in The Chart Series Formula and How to Edit Series Formulas elsewhere on this site, but a brief description here is in order.

Every chart series has a formula which describes the data in the series. The simple chart in this example is shown below its data range; the series is selected to highlight the data used to plot the series and to show the SERIES formula in the formula bar.

``=SERIES(Sheet1!\$B\$1,Sheet1!\$A\$2:\$A\$11,Sheet1!\$B\$2:\$B\$11,1)``

This formula can be broken up into four elements as follows:

``=SERIES([Series Name],[X Values],[Y Values],[Plot Order])``

In our example:

``=SERIES(Sheet1!\$B\$1,Sheet1!\$A\$2:\$A\$11,Sheet1!\$B\$2:\$B\$11,1)``

Sheet1!\$B\$1 contains the Series Name

``=SERIES(Sheet1!\$B\$1,Sheet1!\$A\$2:\$A\$11,Sheet1!\$B\$2:\$B\$11,1)``

Sheet1!\$A\$2:\$A\$11 contains the X Values or Category Labels

``=SERIES(Sheet1!\$B\$1,Sheet1!\$A\$2:\$A\$11,Sheet1!\$B\$2:\$B\$11,1)``

Sheet1!\$B\$2:\$B\$11 contains the Y Values

``=SERIES(Sheet1!\$B\$1,Sheet1!\$A\$2:\$A\$11,Sheet1!\$B\$2:\$B\$11,1)``

and the series is plotted first (1) among the chart’s series collection.

The Series Name can be blank, a text string in double quotation marks, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The X Values can be blank, a literal array of numeric values or text labels enclosed in curly braces, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The Y Values can be a literal array of numeric values enclosed in curly braces, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The Plot Order can only be a whole number between 1 and the number of series in the chart.

## Editing the Series Formula

The series formula is a text-based Excel formula like any other. You can click in the formula bar, and edit the formula manually, to change aspects of the charted series. Select part or all of the formula, type in the text you want there instead, and press Enter to apply the changes (or sometimes to cause an error!). Alternatively you can select one range address in the formula, then click and drag with the mouse to insert the address of another range in the worksheet.

But if you have to do this for every series in your chart, and every chart on the worksheet, you will be editing in the formula bar all day. So let’s make VBA do some of the tedious work for us.

## Editing the Series Formula with VBA

The meat of this technique involves this very simple VBA command:

``````Series.Formula = WorksheetFunction.Substitute(Series.Formula, _
OldString, NewString)``````

Basically, Substitute works by replacing every instance of OldString in the input text string (Series.Formula) by NewString. You could use Replace instead of WorksheetFunction.Substitute, but this would not be compatible with Excel 97.

There are a few tricks I’ve learned to improve the reliability of this technique. First, OldString should be entered using the same case as is found in the formula. For example, “b” will not find any references to column B in the formula. Second, to make sure you don’t trash any worksheet name in the formula, use “\$C\$” rather than “C” if you are replacing references to column C.

In the simplest version of this utility, you can use a couple input boxes to ask the user for OldString and NewString. A little error trapping makes sure that there is an active chart and that OldString is valid.

### Program to Modify the Active Chart

``````Sub ChangeSeriesFormula()
''' Just do active chart
If ActiveChart Is Nothing Then
'' There is no active chart
MsgBox "Please select a chart and try again.", vbExclamation, _
"No Chart Selected"
Exit Sub
End If

Dim OldString As String, NewString As String, strTemp As String
Dim mySrs As Series

OldString = InputBox("Enter the string to be replaced:", "Enter old string")

If Len(OldString) > 1 Then
NewString= InputBox("Enter the string to replace " & """" _
& OldString & """:", "Enter new string")
'' Loop through all series
For Each mySrs In ActiveChart.SeriesCollection
strTemp = WorksheetFunction.Substitute(mySrs.Formula, _
OldString, NewString)
mySrs.Formula = strTemp
Next
Else
MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
End If
End Sub``````

### Program to Modify All Charts on the Active Sheet

Adding another loop to iterate through a sheet’s chart objects will change every chart on the sheet.

``````Sub ChangeSeriesFormulaAllCharts()
''' Do all charts in sheet
Dim oChart As ChartObject
Dim OldString As String, NewString As String
Dim mySrs As Series

OldString = InputBox("Enter the string to be replaced:", "Enter old string")

If Len(OldString) > 1 Then
NewString = InputBox("Enter the string to replace " & """" _
& OldString & """:", "Enter new string")
For Each oChart In ActiveSheet.ChartObjects
For Each mySrs In oChart.Chart.SeriesCollection
mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, _
OldString, NewString)
Next
Next
Else
MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
End If
End Sub``````

### Program to Modify All Charts on All Worksheets of the Active Workbook

Adding another loop to iterate through a sheet’s chart objects will change every chart on the sheet.

``````Sub ChangeSeriesFormulaAllChartsAllSheets()
''' Do all charts in all sheets
Dim oWksht As Worksheet
Dim oChart As ChartObject
Dim OldString As String, NewString As String
Dim mySrs As Series

OldString = InputBox("Enter the string to be replaced:", "Enter old string")

If Len(OldString) > 1 Then
NewString = InputBox("Enter the string to replace " & """" _
& OldString & """:", "Enter new string")
For Each oWksht In ActiveWorkbook.Worksheets
For Each oChart In oWksht.ChartObjects
For Each mySrs In oChart.Chart.SeriesCollection
mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, _
OldString, NewString)
Next
Next
Next
Else
MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
End If
End Sub``````

## How the program works

Here is our chart again, bet we’ve got an extra column of data. The chart shows column B plotted, but we want to plot column C instead.

Using the second version of the program above, all we need to do is run the code. I usually like to select one chart’s series, to remind me what string I want to change.

The program’s first input box asks for the string we want to replace (\$B\$ for column B):

The second input box asks for the string we want to replace that string with (“c”):

We see that the program has changed the series formula, while the chart now highlights the new range and shows the new data.

## Warning: Blank Line or XY Series

When a line or XY series has no plottable points (that is, the data range has no numerical values to plot), VBA can’t read the formula. This example shows the problem.

We have no trouble changing the chart’s Y values from column B

to column C

even though column C has no data to plot. The chart merely looks blank, and we cannot select the series.

When we try to change from column C

to column D

Excel chokes, because it can’t read the series formula we want it to change.

Excel can’t read the formula for a line or XY series with no data, but fortunately it can read other types, so we can do just fine if we change the series temporarily to a column chart, edit the series, and change the series back. Here’s the code:

``````Sub ChangeSeriesFormulaAllCharts()
''' Do all charts in sheet
Dim oChart As ChartObject
Dim OldString As String, NewString As String
Dim mySrs As Series
Dim iChartType As XlChartType
Dim sFormula As String

OldString = InputBox("Enter the string to be replaced:", "Enter old string")

If Len(OldString) > 1 Then
NewString = InputBox("Enter the string to replace " & """" _
& OldString & """:", "Enter new string")
For Each oChart In ActiveSheet.ChartObjects
For Each mySrs In oChart.Chart.SeriesCollection
sFormula = ""
On Error Resume Next
sFormula = mySrs.Formula
On Error GoTo 0
' change to column chart if series is inaccessible
If Len(sFormula) = 0 Then
iChartType = mySrs.ChartType
mySrs.ChartType = xlColumnClustered
End If
mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, _
OldString, NewString)
If Len(sFormula) = 0 Then mySrs.ChartType = iChartType
Next
Next
Else
MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
End If
End Sub``````

The intermediate chart still looks blank, but if we are attentive we might notice the horizontal axis has changed from the typical XY value-style axis to a typical column category-style axis.

After the series is changed, the columns reflect the new values.

Finally, the chart is reverted to its original XY style.

## Warning: VBA Error

If the X value argument of a series formula consists of a Name instead of an array or a range address, VBA incorrectly reads the series formula, by enclosing the Name in single quotes. When assigning a formula to a series with a Name as its X value argument, VBA fails whether or not the erroneous single quotes are included. This is apparently a bug in Excel VBA, and it has been reported to Microsoft. The utility below has been upgraded with a workaround to avoid this error.

Essentially the workaround involves checking for a Name in the X values argument of the formula, temporarily removing the argument altogether from the new series formula, then reassigning the Name using the Series.XValues property.

## The Change Series Formula Utility

These new algorithms have been incorporated into the Change Series Formula function of the Peltier Tech Chart Utility, both Standard and Advanced Editions.

1. Mason says:

Worked Great! Thanks!

2. Cristian says:

“Excel chokes, because it can’t read the series formula we want it to change”

As Microsoft says: http://support.microsoft.com/default.aspx?scid=kb;en-us;213379

Is a great bug… I´m trying to find new ways for a clean workaround. I think I have clean one and would like to share it.

Ok, when you try to do things like:

with activechart.seriescollection(1)
.Name = “Imgoingtocrash”
.Values = blabla
.Xvalues = blabla
end with

it will crash if the series cant find any value (in my case I deleted the cells from which Values and Xvalues were taken)

Well I´ve designed this (for the moment) “clean” workaround:

With ActiveChart.SeriesCollection.NewSeries ‘(in this case will be seriescollection(2))
.Name = “Im not going to crash”
.Values = blabla
.Xvalues = blabla
End With

With ActiveChart
.SeriesCollection(1).Name = .SeriesCollection(2).Name
.SeriesCollection(1).XValues = .SeriesCollection(2).XValues
.SeriesCollection(1).Values = .SeriesCollection(2).Values
.SeriesCollection(2).Delete
End With

With that, SeriesCollection(1) is safe and restored

Hope it helps over there for someone.
Cheers and thanks for your nice web.

3. Anonymous says:

This wroked fantastically – saved hours of my life!

4. George Mitchell says:

John, you have helped me before with some other charting questions, but this is by far the greatest invention since sliced bread! This will shave hours off my updates for my monthly reports! Thank you for sharing this great piece of coding!

5. Great little utility, saved many hours for me!

Only little issue I had is my sheet had a comma in the name and this caused an endless error loop, once I figured that out, all worked perfectly!

6. Yes, there are a few things I haven’t yet written around. Commas in the sheet name, single quotes in the sheet name, etc. But these are things I don’t do much myself, so my motivation is limited.

7. Richie Mulcahy says:

I am running into a problem with this add-in. I am attempting to change x and y values location from a global scope (NEW ASR DATABASE.xls) to a worksheet scope (CF-DATA). Shown below is an example of one of my series formulas:

=SERIES(‘CF-DATA’!\$AL\$25,’NEW ASR DATABASE3.xls’!CF_X23,’NEW ASR DATABASE3.xls’!CF_Y23,17)

Shown below is an example of what I would like it to change to:

=SERIES(‘CF-DATA’!\$AL\$25,’CF-DATA’!CF_X23,’CF-DATA’!CF_Y23,17)

As you can see I am attempting to swap out the ‘NEW ASR DATABASE3.xls’! with ‘CF-DATA’!. I just can’t seem to get it to work, no such change occurs.

8. Richie –

I just tried this on my machine, in both Excel 2003 and Excel 2007, and it changed workbook name (in single quotes) to worksheet name in the series formula with no problem.

Note that the text entered into the “Find old text” box must exactly match capitalization of the text in the series formula.

9. Richie Mulcahy says:

Thank you for your quick response. I must be doing something wrong as I cannot get it to change from workbook name to sheet name. Maybe the issue is when I localised my named ranges, which in turn caused invalid named ranges on my chart? When I then run the add-in, perhaps it cannot find the invalid named ranges?

10. Richie –

Good point. If the current ranges are invalid, and the chart is a line or XY type, VBA cannot access the series formulas. At least it couldn’t in Excel 2003 and earlier. I did a quick test in 2007, and it seems to work regardless of source data validity.

The old trick in 2003 was to change the series to column, make the change, then change back.

11. Anonymous says:

Both versions worked great. Thank you very much for posting these solutions!

12. Anonymous says:

Great utility that saved me loads of time.

Is there anyway of working it to search and replace all graphs within a workbook rather than just the active sheet?

13. Jeff Pierce says:

Works great for data series in charts – any way to extend the idea to error bar series and embedded text in charts that is linked to the worksheet as well?

14. peter says:

great tool – worked nice for me! i’d have the same question as jeff – any way to extend this tool to error bar series?

thanks!

15. Vishal says:

this is a great piece of work.

16. Kenneth says:

This is great for changing columns, do you have an equal way for changing rows using same column?

I have worksheets with 100.000+ rows and like to change the xvalues and values so the user can scroll using buttons.
I have everything ready except an effective way to only change the row part of each seriescollection in chart.

=SERIES(‘sheet1′!\$Q\$1,’sheet1′!\$E\$2:\$E\$10000,’sheett’!\$Q\$2:\$Q\$10000,1)”
=SERIES(‘sheet1′!\$Q\$1,’sheet1′!\$E\$10000:\$E\$20000,’sheett’!\$Q\$10000:\$Q\$20000,1)”

So far I have been working on the formula string using split, mid, instr etc but it’s a lot of iterations to replace the row numbers from each seriescollection and keep the row reference.

Thanks for any good ideas

17. Kenneth –

Go through twice, first changing 10000 to 20000, then changing 2 to 10001.

18. Kenneth says:

Thanks, works fine if I know the chart range, and if the sheet name does not have the same number in it’s name.

I have written some code evaluating the each chartseries formula and replacing minimum row and maximum row.

Sub ChangeChartRowRange(minRow As String, maxRow As String)
Dim oldCF As String
Dim newCF As String
Dim X, Y
Dim ch As ChartObject
Dim s As Series
Dim t1 As String, t2 As String
Dim i As Integer, j As Integer
Dim shN As String
Dim R(1) As String

R(0) = minRow
R(1) = maxRow
For Each ch In ActiveSheet.ChartObjects
For Each s In ch.Chart.SeriesCollection
oldCF = s.Formula
X = Split(oldCF, “,”)

For i = 1 To 2
Y = Split(X(i), “:”)
shN = Left(Y(0), InStr(Y(0), “!”))
For j = 0 To UBound(Y)
t1 = Mid(Y(j), InStr(Y(j), “\$”))
t2 = Mid(t1, InStr(2, t1, “\$”) + 1)
Y(j) = WorksheetFunction.Substitute(t1, t2, R(j))
Next j
Y(0) = shN & Y(0)
X(i) = Join(Y, “:”)
Next i
newCF = Join(X, “,”)
s.Formula = newCF
‘Debug.Print oldCF
‘Debug.Print newCF
Next s
Next ch
End Sub

But I also found a class module written by Walkenbach, ChartSeries that writes and reads among several chartseries properties, the XValues and Values as a range. Not an array. Probably much more effective….

19. You could use \$10000 as the “find” string, and it will not match a number in a sheet name.

20. David says:

Great Utility! Thank you!

I’ve tried to get this to work with R1C1 notation, and it has not worked. Does it work with R1C1 notation? Is there something I’m missing?

I tried to replace C13 with C12 and nothing happened.

Thanks again!

21. David –

Good point. I rarely use R1C1 notation, so I don’t think of it. I’ll have a look when I get a chance.

22. Michael says:

Hi

What is the correct separator for one chart series using data series on different worksheets using Excel 2003?

This gives me an error

=’2011′!\$B\$8:\$M\$8+’2010′!\$B\$8:\$M\$8+’2009′!\$B\$8:\$M8
as does this

=’2011′!\$B\$8:\$M\$8,’2010′!\$B\$8:\$M\$8,’2009′!\$B\$8:\$M\$8

Thanks,

Michael

23. Michael –

You can’t use data from different sheets this way. Your X Values, Y Values, and Series Name don’t need all to be on the same sheet, but each must be contained within one sheet.

24. Robert says:

Is it possible to change all charts on all sheets (ignoring sheets with no charts)?

Thanks

25. Alan says:

I have files built from scratch in Excel 2007 with many Stacked Area charts (1 per sheet) with as many as 25 series in each chart. I was updating these monthly, so you can imagine how long that was taking. Your add-in worked perfectly, so many, many thanks!!

Thanks, Alan

26. Just wanted to say echo Alan’s sentiments above, such a time saver! Thank you!

27. Jim –

Excel doesn’t let you mix constant values and cell references within a single argument in the series formula. What you need to do is use another range, say Q1:Q12. Put your constants in Q1:Q11, and in Q12 put the formula =M31. Then use Histories!Q1:Q12 for your Y values.

28. David Rootes says:

Hi Jon, just tried this in 2010 as I have 120 charts I need to update, It works great on an individual chart, but when I tried to update about a dozen on the active sheet, it only did the first 3 and then destroyed the rest. Can you help?
Cheers, Dave.

29. Brandy says:

This tool is amazing. Saving me loads of time, and I can’t express how much I appreciate you offering it! Thanks thanks thanks!

30. Michael says:

Dear Jon,

This program is really nice and helps a lot! (Sorry for writing on the wrong post in the first place). My question was, if you think this program could also function for the error bar series. This question was asked before but there was no reply. What do you think?

Cheers,
Michael

31. Michael –

Unfortunately, there is no way to get the error bar source data range using VBA. It’s a write-only variable. Since I can’t read it, I can’t use such a nice approach to rewrite it. I wish I could.

32. Melanie says:

This is awesome!!!! Love it and have been looking for something like it since going to Excel 2007. Thanks!

33. I found a “little” problem. I have my own set of procedures and they do work fine .. in most cases. However, recently I wanted to replace series formulas (chart copies pasted on another computer with different paths, so I could not open those originals) by new formulas:
eg. series:

=SERIES(‘D:\wojtek\docs\Research\Magda\kin_single\woda\2,4-D_RIB500\[2,4D_RIB500_fitonly-ex5N2e-12mix.xls]fit’!\$D\$41, ‘D:\wojtek\docs\Research\Magda\kin_single\woda\2,4-D_RIB500\[2,4D_RIB500_fitonly-ex5N2e-12mix.xls]fit’!\$E\$38:\$IJ\$38, ‘D:\wojtek\docs\Research\Magda\kin_single\woda\2,4-D_RIB500\[2,4D_RIB500_fitonly-ex5N2e-12mix.xls]fit’!\$E\$41:\$IJ\$41, 2)

I wanted to replace D:\wojtek\docs by W:\docs (actually change of location).
I opened excel files with series data in new location (must be open to make it work), but my procedures would not work.

I still can change this series manually, but (in VBA) Series().Formula returns

=SERIES(‘D:\wojtek\docs\Research\Magda\kin_single\woda\2,4-D_RIB500\[2,4D_RIB500_fitonly-ex5N2e-12mix.xls]fit’!\$D\$41, ‘D:\wojtek\docs\Research\Magda\kin_single\woda\2,4-D_RIB500\[2,4D_RIB500_fitonly-ex5N2e-12mix.xls]fit’!\$E\$38:\$IJ\$38, , 2)

So Y data was missing!

I do not know whether it is a problem with formula length (over 256 chars), or commas in paths, file names or sheet names (I do not use commas in file name – this is “inherited” problem; a person who supplied the data created such names and I wanted to keep this original tree structure for easier comparisons).

I tried also .FormulaLocal (in Polish version it uses semicolons as separators, so should be less susceptible to comma-related problems, buy the result was exactly the same – missing Y data (perhaps it should not be a surprise, I presume that internally it all works with commas as separators and is later translated to a specific locale).

I tried it on a simpler case (shorter path/file/sheet strings, no commas) and it worked fine.

It is Excel 2002 sp3 (fully patched). I will try it on 2003 and on 2007 later.

So the problem is not changing path names but how Series().Formula works

AWM

PS. I usually use a couple of worksheets for kinetic data analysis etc., but once I update a sheet by adding some new plot (but leaving worksheet structure intact) I copy such plots to other/older worksheets and remove all file references (if the ws/sheet structures are the same) or change all chart data references to the current sheet. Those 2 kinds of chart manipulations are the most useful for me and my macros work fine.

34. Alex says:

You hero! Thank you so much for “The Change Series Formula Utility”

:)

35. The problem with partially empty chart series formulas (see my post above: long paths with commas – results in missing y-data) may be solved in some way by changing worksheet Links instead. It is much quicker than changing chart series one by one, but does not allow to change sheet and data range references (file/workseet references only)! However, it changes ALL referenes to a given worksheet – all charts, alll cell formulas – so you cannot restrict it to some charts or sheets. But… you may move your sheet (or copy chart) temporarily to a new empty worksheet, change links and move/copy back to the original location.

Here is an example of complete solution with comments etc. (it does not check path validity):

dbg = False ‘ change to True only when sth is wrong

oldWarn = “”
oldMsg = “Replace LINKS” & vbCrLf & vbCrLf & _
“Partial path to Replace ” & vbCrLf & vbCrLf & _
Do
oldPath = Trim(InputBox(oldWarn & oldMsg, “old path”))
If (Len(oldPath) = 0) Then
If (dbg) Then Debug.Print “Cancelled: oldPath”
Exit Sub ‘ cancelled
End If

If (dbg) Then Debug.Print “old = “; oldPath
If (InStr(1, oldPath, “:\”) > 0) Then
Exit Do
Else
oldWarn = “Wrong old path!!! – start with Drive letter, L:\path1 ” & vbCrLf
End If
Loop

newWarn = “”
newMsg = “Old Partial path to replace: ” & oldPath & vbCrLf & vbCrLf & _
” (start with Drive letter and root dir, N:\path2 )” & vbCrLf & vbCrLf & _
“CAUTION!!! New file(s) must be already opened!!! ” & vbCrLf & _
“CAUTION!!! New file(s)/sheet path(s) must be valid!!! ” & vbCrLf & vbCrLf & _
“New partial path: ”
Do
newPath = Trim(InputBox(newWarn & newMsg, “new path”, oldPath))
If (Len(newPath) = 0) Then
If (dbg) Then Debug.Print “Cancelled: newPath”
Exit Sub ‘ cancelled
End If

Debug.Print “new = “; newPath
If (InStr(1, newPath, “:\”) > 0) Then
Exit Do
Else
newWarn = “Wrong new path!!! – start with Drive letter, N:\path2 ” & vbCrLf
End If
Loop
‘ path change example (note: procedure does not check path validity!)
‘ oldPath = “D:\wojtek\docs” or “D:\wojtek\”
‘ newPath = “W:\docs” or “w:\”

End Sub

For i = 1 To UBound(aLinks)
Next i
Else
Exit Sub
End If

ccount = 0
For i = 1 To nlinks
If (InStr(1, aLinks(i), oldPath, vbTextCompare) = 1) Then
ccount = ccount + 1
End If
Next i
Debug.Print “Changed: “; ccount
MsgBox (“Found ” & nlinks & ” and Changed ” & ccount & ” ” & LinkTypeName)

End Sub

Enjoy.
AWM

36. AW –

I’m sure my routine falls down because of the commas in the file names. I use a simple Split(string, “,”) to split the series formula at the commas. If the filename contains commas, then there are commas separating SERIES formula arguments and commas within SERIES formula arguments, and the Split command returns an array of gibberish.

John Walkendbach shows a more detailed formula parsing algorithm in A Class Module To Manipulate A Chart Series. This goes character by character, and doesn’t split the string at a comma which is within quotes.

Of course, using commas and other strange characters in filenames violates the Keep It Simple Stupid principle, for what it’s worth.

37. Jon,
I do not use your routines and the problem is not *parsing strings* (if there is a problem, it is internal Excel/VBA problem), but the result given by Series.Formula() in VBA (partally empty). Anyway, I will try the same without commas but with the same path lenghts.

BTW. I myself follow the Way (i.e. Keep It Simple Stupid principle), however, sometimes one has to deal with data produced by someone who thinks in purely “human” not “computer” way.

38. AW –

Parsing strings would be problematic if the strings contained more commas than just those that separated the arguments. Since you use Excel’s Edit Links feature, so commas in the workbook names are not a problem.

Now that I reread your comments, I get what the problem is, missing arguments. ISTR this in the distant past, and if memory serves, it is probably related to length of the strings, and not on any special characters in those strings.

And yeah, sometimes it’s hard to instruct mere mortals to think in ways that make programming more efficient.

39. David Rootes says:

HI Jon, I left a comment /question up above that you did not respond to re lots of charts on one sheet not working in v2010, can you help?
Cheers, David R.

40. chris anderson says:

I downloaded your addin to change chart series and it worked great. However I have multiple worksheets with one chart per worksheets that I need to change the series range across all worksheets. Is this possible? i would prefer vba code rather than an add in.

41. Chris –

It’s possible to do charts on multiple sheets. You need to nest the code one level deeper:

``````For Each oSheet In ActiveWorkbook.Worksheets
For Each oChart In oSheet.ChartObjects
Next
Next``````
42. chris anderson says:

Jon,
I used the code you posted to extend to multiple sheets and it worked perfectly. Thank you so much.

43. Jamie Reilly says:

This has just saved me a full days work of changing data ranges. Thank you!

44. Jose says:

This code works GREAT!!! No problems at all! Thanks so much!!

45. Leavi24 says:

Jon,

I was excited I bumped into this, but I still get the “Unknown Error” Type Mismatched.
Tried it in Excel 2007 and 2010. Is there any fix to it? Please advice.

I have 250 series plotted on one bubble chart and need to change a column reference for each series from \$A\$ to \$B\$.

46. Bubble charts are strange, with an extra argument in the SERIES formula, which isn’t treated exactly the same as a regular range reference. I never tested this program with bubble charts, so I don’t even know how strange their behavior is.

At some point I’ll probably look into bubble chart SERIES formulas.

47. Richard Wood says:

re. David Rootes’ issue with Excel 2010 charts being “destroyed” after only 3 were updated with the series change. I too experienced that issue. I found that if I scroll down past the last chart on the sheet BEFORE clicking on the “Change All Charts on Active Sheet” button, chart series were updated and no charts were “destroyed.” I have no idea why this worked.

48. Shiv says:

Hi, This utility is a great idea unfortunately I get “Unknown Error Type Mismatch” when I use it with Excel 2007, I have a Scatter plot with 3 series –
=SERIES(“AWGN”,’B9′!\$F\$6:\$F\$10,’B9′!\$I\$6:\$I\$10,1)
=SERIES(“Rician, 12/200”,’B9′!\$F\$16:\$F\$20,’B9′!\$I\$16:\$I\$20,2)
=SERIES(“Rician 9/10”,’B9′!\$F\$26:\$F\$30,’B9′!\$I\$26:\$I\$30,3)
and trying to replace \$F\$ with L

49. My algorithm interprets the formula by splitting the series formula into its component parts at the commas in the formula. It isn’t smart enough to know that the comma in “Rician, 12/200” is in the middle of the series name, so it breaks when it tries to interpret 12/200 as the X values.

A future version will correct this shortcoming.

50. Dave Babb says:

Thank You so Much for the chart formula utility! I can’t thank you enough!

Background Story:

I am a disabled ‘Nam Era USMC vet who works as a volunteer at the local VA hospital.

I can tell you that I have undertaken developing a means of tracking and graphing items between the main VA hospital , and 10 satellite hubs in the state in monthly and yearly charts. I ended up with sovereign data (after a few iterations), but needed 840 charts to display all the data on 10 worksheets!

Needless to say changing chart references was genuinely, seriously, painful and time consuming to the extreme.

This utility probably saved me four full days worth of work manually editing the formulas…

As a VA volunteer, I sincerely thank you. It worked like a charm!

Thank you again…… And my old fingers and eyes thank you!

Sincerely and respectfully,

Dave

51. Dustin Smith says:

I feel that Excel 2010’s charting functions are worse off than in 2003 version. I have used this technique sucessfully for years, now, I have defined my ranges, both x and y, but the Chart series function gives me an error, “Function is not valid” yet it actually locates the correct data set in my lookup table. HELP

52. Hi Dustin –
How have you defined your ranges in the past (that doesn’t work now)? Hopefully it only requires a small tweak.

53. Mike says:

Thank you, thank you, thank you, THANK YOU! :)

54. Marko says:

Hi,
I have Excel 2010 and one, two or four series per chart. This add-in works like a chart for charts with only one series, but not for those with two series (haven’t tried with four, yet) Excel crashes and is forced to close.
Any updates for this problem? I would find this VERY USEFUL in my data analysis…

55. Marko –
This works fine on the multi-series charts I use all the time.
What chart type are you using?
Reply to this thread, and include the two series formulas for a chart that can’t be updated without crashing.

56. Dean says:

I have several pages of several complex graphs containing multiple data series. The dead line is HOT!
If only I could set up a page and copy it. But, all the links point to the original data. It’s mind numbing pain to hunt down each reference and change it by hand.

Well, now I CAN copy! This should have been a feature in Excel.

I’ll sleep better tonight and wanted to thank you from the bottom of my heart!

Dean

57. Rick says:

Hi,
I am unable to get the change series formula utilty to work in Excel 2013. Can you tell me if it does work in Excel 2013?
Thanks

58. I’ve been using it in Excel 2013 without a problem. Well, I have a newer in-house version that has some enhancements, but I don’t remember any problems when I first got 2013.

1. Make sure you’ve installed it properly.
2. Look on the Add-Ins tab for the buttons.

59. Alicia says:

This is exactly what I have been looking for! I can’t get the add on to work for me though it keeps saying it can not find the macro. I still wanted to use it though so I used your VBA code and I still got an error on the

mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, OldString, NewString)

line. It says application defined or object defined error. Do you have any idea how I could solve either problem? I made sure my macros were enabled so it is not because of that. Thank you very much.

60. Is the add-in properly installed?

Using the code in your own project: Is mySrs properly declared and assigned to a series? What is mySrs.Formula before this line? What are OldString and NewString? What is the result of the Worksheet.Substitute operation?

61. Rick says:

Hi Jon,

62. Saving a file in the blahblah\Add-Ins directory only makes it available to be installed, but does not install it.
In Excel, go to the add-ins dialog (Office button or File tab > Options > Add-Ins > Go) and check the box in front of the add-in’s name. If the add-in is not shown in the list, click Browse, navigate to the file, and click OK a couple times to return to Excel.
Now the add-in will be always available.

63. Rick says:

The box in front of the add-in Change Series Formula Add-In is listed and it is checked. However, it is not available unless I double click the add-in file where it is saved in order to get the Add-Ins button to appear at the top my Excel worsheet for the add-in to work.

64. Alicia says:

I got it to work properly in my own program. I don’t know how but it works now and it is great! Thank you so much for replying so quickly and for giving this program out to use! It is amazing and saves so much at work! I can’t thank you enough!

65. Karen says:

Jon,
This is such a time saver! I have a workbook with four charts on each tab and about 50 tabs, I just need to change one thing in every chart so part of the name would go from May to June. Is it possible to modify this so it would change all the charts in the workbook?

Thank you very much!

66. In the “ChangeSeriesFormulaAllCharts” routine:
1. Declare this variable
Dim oWksht As Worksheet
2. change this one line
For Each oChart In ActiveSheet.ChartObjects
to these two lines
For Each oWksht In ActiveWorkbook.Worksheets
For Each oChart In oWksht.ChartObjects
3. Before “Else”, insert one more “Next”

67. Rick –
I don’t know what’s not working. The checkbox tells you the file is open, but the lack of buttons on the Add-Ins tab indicates that it is not. Try this:
Uninstall the utility (uncheck the box), move the file to a different directory, then reinstall it from the new directory (using Browse to locate the file).

68. Rick says:

Hi Jon,
As suggested I uninstalled the utility (unchecked the box), moved the file to a different directory, and then reinstalled it from the new directory (using Browse to locate the file). Unfortunately It still does not work in Excel 2013. I then tried it in Excel 2010 and it works fine but not in 2013 which still does not display the add-in button in the toolbar unless the file is double clicked each time you want to use it in order to activate the add-in. Is it possible that xla files work in 2010 but not 2013 and that only xlam files work in 2013? I also played with the macro settings under the Trust Center in 2013 and it did not help. Note that I do not have the entire 2013 suite installed (I only have Excel 2013 the rest of my programs are 2010) and I have both the 2010 and 2013 versions of Excel installed. I don’t know if that makes a difference.

69. Rick –
The behavior you describe is not normal. Both .xla and .xlam files work the same in Excel 2013 (and 2010 and 2007). It should not matter which pieces of which version of Office are installed, or that multiple versions of Excel are installed. Once installed, the add-in should be available and its buttons should be visible, without having to also open the add-in file.
I wonder whether reinstalling Excel 2013 would fix the problem.

70. Federico says:

This is simply great!!!

71. Ken says:

Ran this, and it worked great!!!
Now I’ve added data and need to run it again but I’m getting an error message:
Run time error 1004:
Application defined or object defined error
selecting debug points to this line in the VBA from what I pasted above…
mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, OldString

Any thoughts?

72. Ken –
Hover the cursor over the arguments in the Substitute function. Are mySrs.Formula, OldString, and NewString correct? Type this into the Immediate Window and press Enter:
?WorksheetFunction.Substitute(mySrs.Formula, OldString, NewString)
What is the result? Is it a valid series formula? Does it match what you get if you manually change the data range?

73. John Hackwood says:

Jon like others your site is a very valuable practical resource and just want to say thanks!

74. Dominick Tornabene says:

Jon,
Regarding Ken’s comment: I am getting the same error. Run time error 1004:
Application defined or object defined error
Which is pointing to the mySrs.Formula line. I checked my results by typing ?WorksheetFunction.Substitute(mySrs.Formula, OldString, NewString) and the result was correct. I am completely stumped. The code worked earlier, but now it crashes. Any further insights?

Here is the code I have right now which I have adapted to work on all charts for all worksheets and I have the code replacing a very specific section as my series are all values so no need for the input box:

``````Sub ChangeSeriesFormulaAllCharts()
''' Do all charts in the whole workbook
Dim oWksht As Worksheet
Dim oChart As ChartObject
Dim OldString As String, NewString As String
Dim mySrs As Series

OldString = ",{0"

If Len(OldString) > 1 Then
NewString = ",{#N/A"
For Each oWksht In ActiveWorkbook.Worksheets
For Each oChart In oWksht.ChartObjects
For Each mySrs In oChart.Chart.SeriesCollection
mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, _
OldString, NewString)
Next
Next
Next
End If
End Sub``````

This seems like the issue should be jumping out at me, but I can’t figure out the issue. The dimensions for each variable are correct and the code is looking for series in each of the charts which seems straigh forward. I am really confused.

75. Dominick –
1. What are the complete before and after series formulas?
2. Does the utility work when you enter the snippets using the dialog?

76. Dominick Tornabene says:

Hi Jon,

Okay, I managed to figure out what is causing the issue. Ignore the parenthesis in the examples below. What I am working with is inside the parenthesis.

If there are any number of charts on a sheet that have something I want to replace, for example ( {0, ) with ( {#N/A, ) then there is no issue. The code fails if there is a chart that does not have ( {0, ). So, all charts must have what I am looking to replace. The code worked with the dialogue boxes (as you wrote it) and with my modifications. The only issue is if there is a chart that does not have what I am looking to replace.

Thanks for the quick response. Not sure if there is a work around for this problem, but I’ll keep working on it.

77. After
`For Each mySrs In oChart.Chart.SeriesCollection`
you could use InStr to see whether the series formula contains the “find” string.

78. Dominick Tornabene says:

Hi Jon,

Actually, it looks like the problem is something different. I am able to run the code if charts do not contain what I’m looking for. It seem to be hung up on three charts and I’m not sure what is different about these charts that is causing the problem. There are 27 charts and when I delete the three that are causing the problem, the code works perfect. I am hoping you can help me understand what is different between the series that work and the three that are not working. Keep in mind, the three are not right next to each other; the first is in the middle of all the charts and the last two are the last two charts in my workbook. I have tried running the code on individual sheets and the same charts cause the problem every time. Here is a series of a chart that works:

=SERIES(“Limit”,{41244,41275,41306,41334,41365,41395,41426,41456,41487,41518,41548,41579,41609},{0,17.5,17.5,17.5,17.5,17.5,17.5,17.5,17.5,17.5,17.5,17.5,17.5},2)

Here are the three series that do not work:

=SERIES(“Limit”,{41244,41275,41306,41334,41365,41395,41426,41456,41487,41518,41548,41579,41609},{0,1,1,1,1,1,1,1,1,1,1,1,1},2)

=SERIES(“Limit”,{41244,41275,41306,41334,41365,41395,41426,41456,41487,41518,41548,41579,41609},{0,1,1,1,1,1,1,0,0,0,0,0,0},2)

=SERIES(“Limit”,{41244,41275,41306,41334,41365,41395,41426,41456,41487,41518,41548,41579,41609},{0,0.4,0.4,0.4,0.4,0.4,0.4,0.4,0.4,0.4,0.4,0.4,0.4},2)

Any ideas?

79. Dominick Tornabene says:

Hi Jon,

Sorry for the multiple post, but I think I finally have it figured out. Here is how and when the code fails:

The code checks for all of the series in each chart, therefore, the problem was within series number 1 (which I wasn’t even concerning myself with). I was askig the code to check for all instances of {0 and replace that with {#N/A. So here is the rub, if I have a series that has this combination:

=SERIES(“Actual”,{41244,41275,41306,41334,41365,41395,41426,41456,41487,41518,41548,41579,41609},{0.7,0.74,0.71,0.81,0.7,0.72,0,0,0,0,0,0,0},1)

the code will fail because it tries to replace the {0 (of the {0.7 above) with {#N/A which would give a series of …..{#N/A.7,0.74……

This causes an error and the code stops working. The trick was to ensure I replaced exactly what I want by expanding my find/replace to look like this:

find ,{0, and replace with ,{#N/A,

Works perfect now. Thanks for your help and I hope my problem will provide some insight to others.

80. Dominick Tornabene says:

Hi Jon,

I have posted this on the http://answers.microsoft.com site but I thought I would post this other places just to see if anyone is familiar with this issue.

I have a very peculiar issue with series in charts that have been converted to values once links are broken. I cannot see a discernable pattern of numbers that cause the issue; however, it is very easy to recreate. There needs to be a workbook that contains data and a workbook that contains the chart. On the workbook that contains data, enter in 0.29 for any number of periods, let’s say a total of ten. All periods would be the same number; 0.29.

Now create a line chart with markers in a different workbook and link to that set of numbers (the 0.29 set) and save this workbook. Now there should be two workbooks and the one with the chart is linking to the one with the data. Break the links and save the file with a new name. Clicking on the chart’s series will reveal something that looks like this:

=SERIES(“test”,,{0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29},1)

This appears to be normal and everything is working fine. Now close the two files which will end this instance of Excel and clear all memory. Next, open the file that is not linked and once the series is selected, the error is obvious. This is what the series looks like after the instance has been restarted:

=SERIES(“test”,,{0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999},1)

I believe this is a floating point issue and while this is not an issue within cells, Excel can’t handle the conversion within charts.

Any ideas on a work around?

81. Dominick –
You only alluded to an error. Any specifics?
It looks like a binary/decimal rounding issue. Any reason not to put the data into a range in the parent worksheet, so the chart links to cells?

82. Dominick Tornabene says:

Hi Jon,

Two things. 1. The error is within the action of breaking links of a parent worksheet and restarting the Excel instance. So, there are a number of items Excel charts do not like in this case. For example, some numbers do not display correctly (exactly as you mentioned regarding the binary issue), if the chart was linked to a range of cells that had a blank cell (this would normally be treated as zero but once links are broken there is a #QNAN error), same goes for #NA.

2. The links were in a range of cells in the parent workbook; however, those links must be broken before the file is sent out to others. It is this action of breaking links that causes the problem. The only solution I can think of is to have the values for the charts link to a hidden sheet so that one links are broken, the cells on the hidden sheet convert to values and the charts’ integrety are maintained. The problem with this is all the VBA code I have written for the process. There are thousands of lines of code across multiple modules all designed with the intention of updating a lot of charts. So you can imagine I’m not to keen on this idea.

83. Audrey says:

Hi,
I am working in several barcharts on the same sheet and workbook and was wondering if the code you gave in the section “Program to Modify All Charts on the Active Sheet” is workinng if I want to change the text format in the y axis.

For example, if I have to put a space between the data and the percentage sign for all of them, is this program helpful?

84. Audrey –
The number format is not part of the series formula. However, it’s easy to adjust. Format the legend, and insert the space right into the number format (you may have to define a new custom number format). In other words, change the number format from `0%` to `0 %`.

85. Dominick –
The thing with Excel charts, is that they work best with worksheet ranges. Especially if there is anything non-numeric in the data range.
If you had hidden (or not hidden) sheets that drove the charts, and these sheets linked to data in other workbooks, it would be rather easy to simply convert the cells to their values, and all charts downstream wouldn’t even notice. I’m sure the code would be simpler, and you wouldn’t have to be removing sheets.
Have you considered replacing the charts with pictures of the original charts? Not formattable, but maybe that’s fine.

86. Dominick Tornabene says:

Hi Jon,

I agree with your point and the easiest way to solve my problem is to link the chart data to hidden sheets; however, as I mentioned before, that would involve a ton of code to be rewritten. Making the charts pictures would normally be the best choice; however I need the charts to remain scalable for use in other reports or applications like PowerPoint. At this point I did get a response from Microsoft acknoleding the bug but no word on a fix. See, even if I was to include only numerical values, the charts still have floating point issues. As in my example, just linking to the number 0.29 causes the series in the chart to get whacky. At this point it i faster for me to manually fix any errors that occure after my code has run as this is just a monthly report. Knowing what I do now, I will be mindful of how data is linked to charts in the future to avoid this problem. Bummer though.

87. Dominick –
Yes, I guess I have no easy suggestions for you.

88. Edward Armstrong says:

Jon,

Thanks so much for the post. I am just starting to learn VBA’s so sorry for the beginner question. I have slides with upwards of 40 scatter plots. Is it possible with the macro to select multiple graphs and have that be the Active Chart. I don’t want all 40 to be updated with the new range.

89. Hi Edward-
The utility at the end of the article has this capability. Depending on what’s selected, you have a choice to do the active chart (if one chart is selected), all selected charts (if multiple charts are selected), or all charts on the worksheet (if not all charts are selected).

90. I can’t get it to change anything. Using Excel 2003. I then tried your ChangeSeriesFormula, and it also doesn’t replace anything.
When it executes ” mySrs.Formula = strTemp”, it does not actually change anything in the sheet.
Here’s what I see. I tried to change “Sp500” to “none”.
Watch : : mySrs.Formula : “=SERIES(“W1″,,'[Sp500 lowpe.xls]Staggered’!\$AR\$6:\$AR\$188,1)” : String : Sheet12.ChangeSeriesFormula

Watch : : strTemp : “=SERIES(“W1″,,'[none lowpe.xls]Staggered’!\$AR\$6:\$AR\$188,1)” : String : Sheet12.ChangeSeriesFormula

What can I do? Thanks.

91. Ray –
It seems to have done just what you wanted, changing the string “Sp500” to the string “none” within the larger formula string.
What are you trying to do?

92. Ray says:

It didn’t put the change into the spreadsheet. When I go back and look at the Values in SourceData/Series, it is still Sp500.

Ah-ha! Found it. It won’t change just part of a string inside the single quotes. Excel says the series is: ='[Sp500 lowpe.xls]Staggered’!\$BE\$6:\$BE\$188. I was trying to change 500 to none.
What I had to do was change ‘[Sp500 lowpe.xls]Staggered’ — that is, the old string is everything inside the single quotes, including the quotes.

When I used the Change Series function, I set oldstring to ‘[Sp500 lowpe.xls]Staggered’ and newstring to Staggered. And it worked.

93. The code will change part of the string inside quotes. You just have to specify your strings thoughtfully.

You could have entered [Sp500 lowpe.xls] in the Change What box, and left the Change To box blank. If you put the word none anywhere, Excel treats it as one of the strings you’re using, it doesn’t know that “none” means nothing.

94. Jon,

I downloaded the Add-In and it works great. I’m trying to take the code you gave to be able to edit multiple charts in one workbook but I confess my VBA experience is one college class many years ago.

I’m trying to do this:

In the “ChangeSeriesFormulaAllCharts” routine:
1. Declare this variable
Dim oWksht As Worksheet
2. change this one line
For Each oChart In ActiveSheet.ChartObjects
to these two lines
For Each oWksht In ActiveWorkbook.Worksheets
For Each oChart In oWksht.ChartObjects
3. Before “Else”, insert one more “Next”

I’m not seeing where oChart was declared as a variable in the original code – do I need to do that? I’m also not seeing where “For Each oChart….” (the line that needs to be replaced) is located.

Even if I never get this part to work you’ve already saved me a ton of time so it’s no biggie. I was just trying to increase my VBA skills a little. Sorry if this question is completely ridiculous.

Thanks!

95. Kat –
I’ve added another procedure called `ChangeSeriesFormulaAllChartsAllSheets`. I should probably have included it in the original article. The declarations, as always, are at the top of the procedure.

96. Jon, I found the additional code. Thanks!

I pasted it in and now I’m having the same problem as Ken up above:

“Now I’ve added data and need to run it again but I’m getting an error message:
Run time error 1004:
Application defined or object defined error
selecting debug points to this line in the VBA from what I pasted above…
mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, OldString”

“Ken –
Hover the cursor over the arguments in the Substitute function. Are mySrs.Formula, OldString, and NewString correct? Type this into the Immediate Window and press Enter:
?WorksheetFunction.Substitute(mySrs.Formula, OldString, NewString)
What is the result? Is it a valid series formula? Does it match what you get if you manually change the data range?”

What window should I paste this into? Basically what I’m trying to do is change all of the series with CZ to DA. I’m compiling charts that stay updated on a 12 month basis and DA is the beginning of the last 12 months. This worked fine with the other macro, updating each chart individually, but I’ve got almost 50 charts so you can see why it would be better to get all of them at once :)

– Kat

97. The Immediate Window is part of the VB Editor. Press Ctrl+G to show this window. It is a tremendous way to interact with VBA.
Keep in mind that series formulas include not just the cell addresses, but also the worksheet names. You may have to change these as well if the data resides on individual sheets.

98. Thanks, Jon – tried that too. I don’t get the error message anymore – now it runs like there’s no bugs but it doesn’t change my charts.

Don’t worry about it, I think I just don’t have enough VB knowledge to properly troubleshoot. What you’ve put out there to download works great. Thank you :)

99. Bill Otto says:

Do you have any tips/tricks for using ActiveX Scroll Bars (sliders) for changing series data? I’m trying to simulate an oscilloscope using an Excel Chart. I searched your website (and the world) and can’t find any help.

100. shaun says:

Just added the ad on and its great ive needed this for years lol.

101. Edward Armstrong says:

Jon,

This is a great trick. I use this VBA all the time. Is it possible to strike a middle ground between 1 chart and all charts. I want to modify 10 graphs on a chart on the same time when they are selected. Could you please advise?

-Ed

102. Ed –
Actually, the utility detects when more than one chart is selected. In that case it offers to change all selected charts or all charts in the worksheet.

103. Trevor says:

I am using trying to change the worksheet name reference on an XY plot. The add-in works great for changing the X value portion, but does not change the name or the Y value references. How can I get these to change as well?
Thanks
Trevor

104. Trevor –
What are the before and after series formulas?

105. Trevor says:

Jon
Here is one of the series equations:
=SERIES(Master!N_04,Master!X_04,Master!Y_04,1)
I would like to replace the Master! with Week_42! and do this for 25 similar series equations.
I am not sure what I was doing different yesterday, but today none of the portions (series name, x data, y data) are changing.
Thanks
Trevor

106. Rebecca says:

If all your charts are chart objects, this worked for me. (i always change the same thing so I took out his prompts for old string and new string.

Sub ChangeSeriesFormulaAllChartsAllSheets()
”’ Do all charts in all sheets
Dim OldString As String, NewString As String
Dim c As Chart
Dim ChartSeries As Series

OldString = “\$D\$”
If Len(OldString) > 1 Then
NewString = “B”
For Each Chart In ActiveWorkbook.Charts
For Each ChartSeries In Chart.SeriesCollection
Dim s As String
s = ChartSeries.Formula
s = Replace(ChartSeries.Formula, OldString, NewString)
ChartSeries.Formula = s
Next ChartSeries
Next Chart
Else
MsgBox “Nothing to be replaced.”, vbInformation, “Nothing Entered”
End If
MsgBox “Update Complete.”, vbInformation
End Sub

107. Tim Wilkinson says:

John,
In the example, what I need to change is =SEIRIES(Sheet1\$b\$1, Sheet1!\$A\$2:\$…….
to Sheet2 with all the other content the same.

108. Tim –
If the sheet names are in fact Sheet1 and Sheet2, then OldString is Sheet1 and NewString is Sheet2. If they are different, OldString is the old sheet name with single quotes if the sheet name is surrounded by single quotes in the series formula; NewString is the new sheet name surrounded with single quotes (if they are not needed, Excel will ignore them).

109. Jenny says:

This is fantastic! I had no idea it was possible, can’t believe it worked so well. Saved hours of very tedious work. Many thanks.

110. Greg Lill says:

Jon, the first 4 sheets in my workbook have about 40 charts each. Your code was perfect to enable updating all of the data series in each chart on each sheet, but sometimes (like on sheets 2, 3 and 4 but not on sheet 1) the plot areas get moved so the charts end up jumbled together. Has anyone else run into this? If so, any suggestions on how to either keep this from happening or, using VBA, put the plot areas back in their original position? Thanks.

111. Greg –

I haven’t ever seen this formatting of charts while changing series formulas.

What version of Excel are you using? What types of charts are these (scatter, line, column,…)?

112. Greg Lill says:

Jon, I’m using Excel 2010. The charts are mostly line charts but there are a few other types (area, stacked bar, combination line/bar, combination line/area.) The funny thing is, when the plot areas get moved, they get moved for all chart types on a given sheet. Meanwhile, on another sheet, nothing gets moved. By the way, based on my Googling, no one else has seen this either.
I wish I had something to show you, but visualize, for example, the left edge of the plot area in Column AT before the routine runs, and Column AP afterwards, and the top edge in Row 4 before and Row 3 after. Since I don’t think I’ll be able to figure this out, is there a VBA way to select all plot areas and reposition them en masse?

113. Greg Lill says:

Well, problem solved, and of course it was something seemingly unrelated to what I thought I was doing. I’m not sure if this has general applicability, but I thought I’d close the loop anyway – the zoom factor on the sheets that were getting jumbled was greater than 100%. When I reset it to 100% before running the macro (needless to say, I didn’t create the workbook with all of the charts, just the macro to automate updating the data series), everything came out fine. Thanks again for your code which saves us countless hours every month.

114. Greg –

I have heard of the zoom affecting formatting, but since I rarely mess with it, I never think to check the zoom.

115. Andrew says:

I have an error when changing from column name with two letters to a column name with one letter. I’m not sure if other people are having this problem but this is what is occurring for me. I used the code for modifying the series for a selected chart (first code snippet). If I do something like \$d\$ to c it works fine. If I do \$cm\$ to \$ba\$ it works. When i try to do \$cm\$ to d all of the series are deleted from the chart. Is this a known error? Help would be appreciated.

116. Andrew –

Using `ChangeSeriesFormula` under the section “Program to Modify the Active Chart”, I changed a series from \$AF\$ to c and from \$C\$ to af repeatedly, no problems. Do all of the ranges contain data? Are the series actually deleted, or are they pointed to blank ranges? What are the before and after series formulas?

117. Andrew says:

Thank you for the reply. I found the issue and it was NOT in your macro. In fact I observed the same behavior even when I didn’t use the macro. The series I was changing referenced a data column. The column to which I was changing the series to was hidden in that tab. Whenever I changed the reference to a hidden column the data series disappeared! Is this a known bug. When I unhid the column the data series re-appeared. Perhaps I am just a noob but I had no idea that this happened.

On a separate note, I have text boxes on these chart which fill with the statistics for a formula (using the text box linked to a cell). Is there any way to do a VB script to change these references like for the chart? I can ask this question in another place if more appropriate. Thanks again.

118. Andrew –

That’s not a bug. That’s Excel’s default for plotting of hidden data. To change this for a chart, select the chart, click the Select Data button on the ribbon, then click the Hidden and Empty Cells button in the bottom left of the dialog. Check Show data in hidden rows and columns, and click OK a couple times to get back to Excel.

119. atul amin says:

i am having one excel sheet containing various different figures which is to be shown in another excel sheet. How to do

120. Atul –

If by figures you mean charts, copy each chart and paste onto the other sheet.

If by figures you mean data in cells, copy the data, select the target range on the other sheet, and use paste special – links.

121. Rich Greves says:

I get the same runtime error (1004) as Ken, Dominick and Kat. I’ve tried all of the suggestions and it still doesn’t work. I’m stumped, any ideas?

122. Rich Greves says:

I found the error, although, I can’t explain it. I was trying to have my charts changed to point to a different sheet. The new sheet had a space in it’s name. When I removed the space, the error disappeared, and the routine ran perfectly. Thanks.

123. A more complicated algorithm is required if the sheet name being changed from or to contains a space, hyphen, or certain other characters. I didn’t include the algorithm here but it’s built into my Chart Utility.

124. jhane says:

how to use this series formulas??

125. Luis says:

Hi there,
I’m not able to change the series ranges using the chart utility, I get the following vba error:
Unknown error
Method ‘Chart Type’ of object ‘Series’ failed

126. Alex says:

Hello. This is awesome. A question though: I have a different chart for every row. So, while this saves me time by not having to change each series within each chart, I still have to run the macro for each chart. It’d be really cool, if you could match the chart title to the row and make the replacement of that criteria. Not sure if that makes sense. Let’s say I have 200 rows, and each row is a chart. When I create the first chart, it will be five series from row 1 (5 years across columns). The next chart will be five series from row 2. Obviously, I would copy and paste to create the chart, but I’d have to run the macro on each of these charts. Not sure if there is a better way. Thanks.

127. Stefanie Schutte says:

Hi Jon

Thanks so much for this routine. It saved me days of work!

128. Daniel says:

És mágico

129. Hangman says:

Hi Jon,

So, I’m moving selected worksheets from one workbook to a new workbook and want to change the series data workbook name for all my charts from the original (old) workbook to the new workbook. I’m grabbing the following code to integrate with my existing code.

``````    For Each ws In ActiveWorkbook.Worksheets
For Each oChart In ws.ChartObjects
For Each mySrs In oChart.Chart.SeriesCollection
mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, "'Original Workbook.xlsm'", "'New Workbook.xlsx'")
Next
Next
Next ws``````

I’m basically wanting to change this:

=SERIES(” Total Forecast”,{“M1″,”M2″,”M3″,”M4″,”M5″,”M6″,”M7″,”M8″,”M9″,”M10″,”M11″,”M12″},’Original Workbook Name.xlsm’!My_Forecast,1)

to this

=SERIES(” Total Forecast”,{“M1″,”M2″,”M3″,”M4″,”M5″,”M6″,”M7″,”M8″,”M9″,”M10″,”M11″,”M12”},’New Workbook Name.xlsx’!My_Forecast,1)

but everytime I run the code it hangs excel and I get a similar error to some that other people have experienced, i.e. run-time error ‘1004’, telling me to verify your formula contains a valid path, workbook, range name and cell reference and I can’t figure out why? I thought it may relate to the fact that the new string doesn’t actually exist because the newly created file is saved after running the change data series code but I also tried adding the code after saving the file but that didn’t work either.
Where am I going wrong?

This is my full code:

``````Sub MyReport()

Dim wb As Workbook
Dim ws As Worksheet
Dim varMySheet As Variant
Dim pt As PivotTable, arr, rng As Range, i As Long, FName
Dim oChart As ChartObject
Dim mySrs As Series

Application.ScreenUpdating = False

For Each varMySheet In Array("Data", "Lists")
Sheets(varMySheet).Visible = xlSheetVisible
Next varMySheet

Set ws = wb.Worksheets(1): arr = Array("My Report", "North", "South", "East", "West", "Pivot", "Data", "Lists")

ThisWorkbook.Worksheets(arr).Copy Before:=ws

For i = 1 To UBound(arr) + 1
For Each pt In Worksheets(i).PivotTables
ws.Cells.Clear: Set rng = pt.TableRange2: rng.Copy
ws.Range("A1").PasteSpecial (xlPasteValues)
ws.Range("A1").PasteSpecial (xlPasteFormats)
rng.Clear: ws.Range("A1").CurrentRegion.Copy rng
Next pt
Next i

For Each ws In ActiveWorkbook.Worksheets

ws.Cells.Copy
ws.[A1].PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Cells(1, 1).Select
ws.Activate
Next ws
Cells(1, 1).Select

i = UBound(arr) + 2

While wb.Worksheets.Count >= i
wb.Worksheets(i).Delete: Wend

Worksheets("My Report").Activate

For Each varMySheet In Array("Data", "Lists")
Sheets(varMySheet).Visible = xlVeryHidden
Next varMySheet

For Each ws In ActiveWorkbook.Worksheets
For Each oChart In ws.ChartObjects
For Each mySrs In oChart.Chart.SeriesCollection
mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, "'Original Workbook.xlsm'", "'New Workbook.xlsx'")
Next
Next
Next ws

ActiveWorkbook.Names("Formula").Delete
Application.ScreenUpdating = True

FName = Application.GetSaveAsFilename(InitialFileName:="C:\My Folder\My Report.xlsx", fileFilter:="Excel workbook (*.xlsx), *.xlsx")
On Error Resume Next: If FName  False Then wb.SaveAs FName

If Err.Number  0 Then
Application.Dialogs(xlDialogSaveAs).Show
Err.Clear
End If
End Sub``````

I’ve tried entering the full filepath instead of just the filename but to no avail? Any ideas?

130. There’s a lot of code there, and it’s hard to follow. You should avoid the urge to put multiple lines onto the same line of code separated with colons.

Don’t use `ws.[A1]`, instead use `ws.Range("A1")`.

However, if you haven’t saved the file yet, you can’t use the new (unused) file name in any formulas. What is the filename before you edit the formula? If the chart exists in ‘Old Filename’, it exists in ‘New Filename’, and the links will update to ‘New Filename’ automatically.

131. Hangman says:

Hi Jon,

Thanks for your reply and apologies for the confusing code. To simplify things I will briefly explain what the code does and then what I’m trying to achieve with the chart source data.

‘Original Workbook’ – is called ‘Causeway Official Return (Regional) 2015_2016.xlsm’

‘New Workbook’ – the default filename is ‘Finance Report.xlsx’ but the user can give the file any name they like.

To try and simplify things I’m going to focus on one Worksheet only, ‘Pivot Table Report’. The code itself moves three Worksheets in total.

‘Pivot Table Report’ – contains data for two charts (rows and columns of numbers) and four actual charts
‘Source Data’ – contains the data for the other two charts on ‘Pivot Table Report’
‘Lists’ – contains various named ranges used in the Worksheet ‘Source Data’

Please Note: The charts are ‘NOT’ Pivot Charts.

The code simply copies the source data and pivot table data in each worksheet and pastes it as values so there are no links to the ‘Oiriginal’ workbook.

It is the ‘Pivot Table Report’ Worksheet containing four charts where I am trying to change the reference to the chart source data. Two of the charts (column charts with two series) use data on the ‘Pivot Table Report’ Worksheet itself and these are no problem at all.

The other two charts (line charts with two series) appearing on ‘Pivot Table Report’ use data on the ‘Source Data’ Worksheet. These are the two where when moved to the new workbook the ‘=SERIES’ still references the ‘Original Workbook’ and which I need to change so they reference the ‘New Workbook’.

The Two Line Charts
When I look at the ‘SERIES’ for the line charts in my ‘Original Workbook’, this is how it appears, referencing the ‘Original Filename’, ‘Causeway Official Return (Regional) 2015_2016.xlsm’

=SERIES(” Cumulative Forecast,{“M1″,”M2″,”M3″,”M4″,”M5″,”M6″,”M7″,”M8″,”M9″,”M10″,”M11″,”M12”},’Causeway Official Return (Regional) 2015_2016.xlsm’!North_Forecast,1)

‘North_Forecast’ in the ‘SERIES’ above references a named range defined as:

=IF(MOD(COLUMN(‘Source Data’!\$D\$5:\$O\$5),1)=0,’Source Data’!\$D\$5:\$O\$5,NA())

When I look at the same chart in my ‘New Workbook’ the ‘=SERIES’ still references the ‘Original Workbook’ but what I want to do is to change the reference to the ‘New Workbook’. I tried using this code to achieve this:

``````    Dim ws As Worksheet
Dim oChart As ChartObject
Dim mySrs As Series

For Each ws In ActiveWorkbook.Worksheets
For Each oChart In ws.ChartObjects
For Each mySrs In oChart.Chart.SeriesCollection
mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, _
"'Causeway Official Return (Regional) 2015_2016.xlsm", _
"Finance Report.xlsx")
Next
Next
Next``````

At this point ‘Finance Report.xlsx’ doesn’t exist because the new workbook hasn’t yet been saved, which supports your comment:

‘If you haven’t saved the file yet, you can’t use the new (unused) file name in any formulas.’

so I tried changing “Finance Report.xlsx” to wb.name which didn’t work, however you then go on to say:

If the chart exists in ‘Old Filename’, it exists in ‘New Filename’, and the links will update to ‘New Filename’ automatically.

The charts do exist in ‘Old Filename’ but when moved to ‘New Filename’ the ‘=SERIES’ still references ‘Old Filename’ and it is this that I am trying to change to ‘New Filename’. My ‘=SERIES’ in ‘New Filename’ should look like this.

=SERIES(” Cumulative Forecast,{“M1″,”M2″,”M3″,”M4″,”M5″,”M6″,”M7″,”M8″,”M9″,”M10″,”M11″,”M12”},’Finanace Report.xlsx’!North_Forecast,1)

and that is where I am stuck because it isn’t working.

I’ve annoted the full code to hopefully make it easier to understand and to spot and glaring errors. Apologies for the lengthy post and many thanks for taking the time to help.

``````Sub FinanceReport()

Dim wb As Workbook
Dim ws As Worksheet
Dim varMySheet As Variant
Dim pt As PivotTable, arr, rng As Range, i As Long, FName
Dim oChart As ChartObject
Dim mySrs As Series

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'Unhide two worksheets in the original workbook so they can be moved to the new workbook

For Each varMySheet In Array("Source Data", "Lists")
Sheets(varMySheet).Visible = xlSheetVisible
Next varMySheet

'Identify three worksheets from the source workbook, put these in an array to 'move' to
'a 'new blank workbook' that has not yet been saved

Set ws = wb.Worksheets(1): arr = Array("Pivot Table Report", "Source Data", "Lists")

'Cycle through each of the three worksheets in the new workbook and where a pivot table
'is located, copy the data and paste as values and formats

ThisWorkbook.Worksheets(arr).Copy Before:=ws

For i = 1 To UBound(arr) + 1
For Each pt In Worksheets(i).PivotTables
ws.Cells.Clear: Set rng = pt.TableRange2: rng.Copy
ws.Range("A1").PasteSpecial (xlPasteValues)
ws.Range("A1").PasteSpecial (xlPasteFormats)
rng.Clear: ws.Range("A1").CurrentRegion.Copy rng
Next pt
Next i

'Cycle through each of the three worksheets in the new workbook and where it
'finds non pivot table data e.g. formula, copy the data and paste values

For Each ws In ActiveWorkbook.Worksheets

ws.Cells.Copy
ws.Range("A1").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Cells(1, 1).Select
ws.Activate
Next ws
Cells(1, 1).Select

i = UBound(arr) + 2

While wb.Worksheets.Count >= i
wb.Worksheets(i).Delete: Wend

'Change the reference to the chart source data from the orignal workbook to the new workbook

For Each ws In ActiveWorkbook.Worksheets
For Each oChart In ws.ChartObjects
For Each mySrs In oChart.Chart.SeriesCollection
mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, _
"Causeway Official Return (Regional) 2015_2016.xlsm", _
"Finance Report.xlsx")
Next
Next
Next

'Make the 'Pivot Table Report' the default worksheet shown

Worksheets("Pivot Table Report").Activate

'Hide the Source Data and Lists tabs by making them xlVeryHidden

For Each varMySheet In Array("RPM - Cumulative Data", "Lists")
Sheets(varMySheet).Visible = xlVeryHidden
Next varMySheet

'Delete the named range called 'Selection'

ActiveWorkbook.Names("Selection").Delete

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

'Invoke the 'SaveAs' dialogue window, populated with a default filename,
'allowing the user to Save the new workbook

FName = Application.GetSaveAsFilename(InitialFileName:= _
"C:\Reports\Finance Report (" & Format(DateAdd("m", -1, Now), "mmmm") & ").xlsx", _
fileFilter:="Excel workbook (*.xlsx), *.xlsx")

On Error Resume Next: If FName  False Then wb.SaveAs FName

'If the user is warned that the file already exists bring up the 'SaveAs'
'dialogue window again allowing the user to save the file using a new name

If Err.Number  0 Then
Application.Dialogs(xlDialogSaveAs).Show
Err.Clear

End If

End Sub``````
132. Hangman says:

As a follow up I took my ‘New Workbook’ with the charts referencing the ‘Old Filename’ and ran your code (below), entering the OldString and NewString but sadly I get the same problem, Excel hangs and I have to Force Quit, so I can only assume this has something to do with the way the charts are created or am I missing something obvious?

The Errors I am getting are both Run-time ‘1004’ and Run-time ‘2023’ Application defined or object-defined error and it refers always to:

`mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, OldString, NewString)`

``````Sub ChangeSeriesFormulaAllChartsAllSheets()
''' Do all charts in all sheets
Dim oWksht As Worksheet
Dim oChart As ChartObject
Dim OldString As String, NewString As String
Dim mySrs As Series

OldString = InputBox("Enter the string to be replaced:", "Enter old string")

If Len(OldString) > 1 Then
NewString = InputBox("Enter the string to replace " & """" _
& OldString & """:", "Enter new string")
For Each oWksht In ActiveWorkbook.Worksheets
For Each oChart In oWksht.ChartObjects
For Each mySrs In oChart.Chart.SeriesCollection
mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, _
OldString, NewString)
Next
Next
Next
Else
MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
End If
End Sub``````
133. This is becoming like a consultancy project. I see many things I would do differently. But anyway.

I don’t understand what your Name is doing. `MOD(COLUMN(any range),1)` should always = 0, so the Name should always reference the range.

While Names can be used in charts, they cause a lot of heartache. According to comments in the extended ChangeSeriesFormula routine in my commercial software, Names are not always correctly processed in the series formula, and my code takes pains to detect this, bypass editing the series formula, and fill in the series using the `.Values`, `.XValues`, and `.Name` properties of the series.

If in fact your Name is doing something that I don’t understand from my simple recreation, could you instead put the calculations into a worksheet range nearby, and use that range in the chart instead of the Name? Charts were designed to use worksheet ranges, not Names or literal arrays (which can also be problematic). This may avoid a lot of problems beyond just the simple code in this tutorial not working.

134. Hangman says:

The other ‘interesting thing is that if I select the plotted line on my chart (in my ‘New’ Workbook) with both the ‘Original’ and the ‘New’ Workbooks open, the ‘=SERIES’ formula displays in the formula bar as:

=SERIES(” Cumulative Forecast,{“M1″,”M2″,”M3″,”M4″,”M5″,”M6″,”M7″,”M8″,”M9″,”M10″,”M11″,”M12”},’Causeway Official Return (Regional) 2015_2016.xlsm’!North_Forecast,1)

If I edit the Series by clicking on ‘Select Data’ followed by ‘Edit’, the ‘Series Values’ dispaly as

=’Causeway Official Return (Regional) 2015_2016.xlsm’!North_Forecast

If I have just my ‘New’ Workbook open and do the same thing, I get nothing shown in the formula bar at all when selecting a plotted line on my chart and when I Edit the Series it dispalys as

[1]!North_Forecast

Not sure if there is any significance in that or whether that is what would be expected, but thought it might be helpful.

135. `[1]!North_Forecast`

This kind of thing happens when Names fail in charts. I used to see it all the time in Excel 2007. I haven’t seen it much lately, probably because I don’t use Names much in charts.

136. Instead of copying some sheets into a new workbook and saving the new workbook, what if you save the main workbook under the new name and delete unneeded worksheets? This would prevent problems with a name moving to a new workbook and not knowing where it should point to.

This would be a less powerful change than not using Names at all, but it would also simplify a lot of your routine.

137. John Simpson says:

Hi, I am not very familiar with Excel and trying to learn. I am trying to copy charts from one sheet to another. After copying I want to change the sheet name in the series formulas,
I have copied the code above into a macro, but every time I try to run it, it hangs at:-

mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, _
OldString, NewString)

When I click on this in the debugging screen I get the following message:-

mySrs.Formula =

What can I do to correct this problem?

138. John –

In Make a Copied Chart Link to New Data I show the best way to copy a chart to a new sheet and link the chart to the new sheet’s data. In fact, it is to copy the sheet with the chart, then copy the new data on top of the copied sheet’s data. The copied chart already links to the correct range on the correct sheet, you just need to change the data.

However, if you’re going to use the copy-chart-and-change-chart-links approach, I have a couple questions:
What are you using for NewString and OldString
What is the series formula of the first series in the copied chart before running the code?
Also, the message you posted didn’t make sense.

Replace this line:

``````mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, _
OldString, NewString)``````

with this:

``````Dim sNewFormula As String
sNewFormula = WorksheetFunction.Substitute(mySrs.Formula, _
OldString, NewString)
mySrs.Formula = sNewFormula``````

Run the new code and tell me where it breaks.

139. John Simpson says:

Hi Jon

Thanks for your quick reply. All the series formulas are of the format =Sheet10!Test1 where Test1 is a defined name on the same sheet.
Each chart has two series with one series name and an axis label range. All have the same formula format.
Each sheet has up to 32 charts.

When I copy the sheet with the charts the defined name does not copy correctly. However, if I delete all the charts from the copied sheet, then copy and paste the charts from the original sheet, the defined names are correct, but the sheet number is incorrect.

What I am trying to do is to change Sheet10 to Sheet11 in all the series names, values and axis label range, when I have copied the charts from the original sheet.

I cannot find a way to keep the defined names correct when I copy the sheet with the charts, so I thought that this may be the answer.

I have copied the new code you sent:-

Dim sNewFormula As String
sNewFormula = WorksheetFunction.Substitute(mySrs.Formula, _
OldString, NewString)
mySrs.Formula = sNewFormula

Then I ran the macro.

It actually changed the sheet number in a couple of instances in the second chart on the sheet, but only on that sheet, and not all the formulas were changed.

Then the macro hung up at:-

mySrs.Formula = sNewFormula

With the error message

mySrs.Formula = sNewFormula = False

I hope you can sort this out for me as it would save me hours of work.

140. John Simpson says:

Hi Jon

I have just noticed an error in my latest message

I wrote:-
It actually changed the sheet number in a couple of instances in the second chart on the sheet, but only on that sheet, and not all the formulas were changed.

What I should have written was:-

It actually changed the sheet number in a couple of instances in the second chart on the sheet, but only on that chart, and not all the formulas were changed.

141. John Simpson says:

Jon

I have solved the problem.
For some reason it was a problem with the defined names I was using in my formulas.
I was using three formulas in each chart, each containing a different defined name.
After trying different options I found that if all the defined names started with the same letter, everything works fine.
Therefore I renamed all my defined names simply by putting an A in front of every name.
There is no longer a problem.

I don’t know if you have encountered this problem before, and if you know why it is happening. Because I was not trying to change the part of the formula containing the defined name, only the sheet number.

However, all is running smoothly now. This is going to make life a lot easier.
Thank you.

142. John –

What were the original and new Names of the chart data ranges? Were you trying to just change the sheet number (the “1” in “Sheet1”) or the entire sheet name (“Sheet1”)?

If all you are doing is changing the sheet name, then there are other approaches which may be more robust.

143. John Simpson says:

Jon

The original data ranges where:-
=Sheet1!Tim1
=Sheet1!Ret1
=Sheet1!Lim1
Each chart, up to a maximum of 32 charts per sheet had sequential numbers at the end of the data range formulas.
I am changing the Sheet1 to each new sheet number as I create new information. It is not possible just to try to change the “1” as the figure appears in the equations on more than one occasion, and it is only the sheet number that I want to change.
Once I changed the data range formulas to:-
=Sheet1!ATim1
=Sheet1!ARet1
=Sheet1!ALim1
everything works fine.

144. John –

Now I see the problem. Your Name names are confusing Excel, and in fact I’m surprised Excel even allows you to use them.

The last column in Excel is XFD, so Tim1, Ret1, and Lim1 ambiguously refer to cells in the first row of columns TIM, RET, and LIM. You don’t need them to start with the same character, they just need to be beyond XFD. So names like Time1, Return1, and Limit1, or even Tim_1, Ret_1, and Lim_1 would have worked.

145. Cem says:

Really amazing macro!!! Thank you so much, it worked perfectly! I tried both single graph and sheet graphs macros, both are really helpful. They really saved me so many hours from being spent for repeated actions :) Thank you thank youu :)

146. Maelren says:

Saved me A LOT of work, thank You!

147. Michelle says:

I am trying to use the code to only change one chart. My charts are identical across all worksheets. I just need to change the worksheet name. I am getting the error code 445: Object does not support this action.

Any feedback would be greatly appreciated.

Thanks
Michelle

148. Hi Jon,

Not sure what’s happening here but I suspect it’s because I’m using Named Ranges as my chart series. I’m wanting to switch everything from a series of data named “Personal” to a series named “Commercial” – everything looks correct when I check the series in the Immediate window but when I step through to the line that actually commits the change (mySrs.Formula = …..) I get a Run-time error ‘1004’ Application-defined or object-defined error.

Results of my Immediate window are…
?OldString
Personal
?NewString
Commercial
?mySrs.Formula
=SERIES(‘Rolling 25 Charts’!\$A\$4,’Motor Assessing Reporting Suite – Tableau Commercial Insurance.xlsm’!Rolling25Dates,’Motor Assessing Reporting Suite – Tableau Commercial Insurance.xlsm’!MA1.2PersonalInsuranceNSWACTAverageCostAdjusted,1)
?WorksheetFunction.Substitute(mySrs.Formula,OldString,NewString)
=SERIES(‘Rolling 25 Charts’!\$A\$4,’Motor Assessing Reporting Suite – Tableau Commercial Insurance.xlsm’!Rolling25Dates,’Motor Assessing Reporting Suite – Tableau Commercial Insurance.xlsm’!MA1.2CommercialInsuranceNSWACTAverageCostAdjusted,1)

As you can see the only change is the “!MA1.2PersonalInsurance” becomes “!MA1.2CommercialInsurance” which is the correct named range. Any ideas what might be stopping things? Excel 2016 is my application version.

149. Bob –

The problem is with Names that begin with the letters ‘C’ or ‘R’, an error that was introduced in Excel 2007 and still persists. Your Y values are fine (MA1.2PersonalInsuranceNSWACTAverageCostAdjusted or MA1.2CommercialInsuranceNSWACTAverageCostAdjusted). The problem is the X values (Rolling25Dates). Change that to any other letter (XRolling25Dates) and it should work.

150. Thanks Jon, greatly appreciated. I switched to XRolling25Dates as suggested and the code ran fine.

151. Michelle –

What line of code is highlighted when you see this error?

152. Jeff says:

I’ve got an older version of the addin PTS_ChangeSeriesFormula, it saved me a lot of time. For some reason the option to change the data series doesnt come up anymore although the addin is enabled, any ideas? Ive seen a lot of workaround but none of them have worked for me. I notice the adding is no longer free and only planned ot use this specific feature so would prefer to stay on the old version.

153. Jeff –
A recent Windows Update changes the way Windows treats files which were not created on your own computer. For security reasons these files are blocked.
I’ve described the problem and offered a couple of fixes in Ribbon Disappears from Excel.