I have a macro that copies elements of a Template worksheet to create
new sheets. The new sheets are for the most part identical except that
the data values are different. I need to copy a chart that is on the
template sheet to the other sheets and have it pick up the same
sources, just with the new sheetname. Just copying the chart and
pasting results in a chart referring back to the template sheet.

Any Ideas where I should start?

Thanks
Robert

You can do it the hard way: Copy a sheet of data, change the data, copy a
chart, and change the chart's links.

Or you can do it the easy way: Copy a sheet with data and chart, change the
data, and watch the chart automatically update.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Robert H" wrote in message
oups.com...
I have a macro that copies elements of a Template worksheet to create
new sheets. The new sheets are for the most part identical except that
the data values are different. I need to copy a chart that is on the
template sheet to the other sheets and have it pick up the same
sources, just with the new sheetname. Just copying the chart and
pasting results in a chart referring back to the template sheet.

Any Ideas where I should start?

Thanks
Robert

Thanks John, unfortunately I have built this app over several years
and its a hybrid of the easy and the hard.... can you provide, or
point to, an example of how to change a charts links using VBA?

Robert

On Feb 20, 11:27 pm, "Jon Peltier"
wrote:
You can do it the hard way: Copy a sheet of data, change the data, copy a
chart, and change the chart's links.

Or you can do it the easy way: Copy a sheet with data and chart, change the
data, and watch the chart automatically update.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutionshttp://PeltierTech.com
_______

"Robert H" wrote in message

oups.com...

I have a macro that copies elements of a Template worksheet to create
new sheets. The new sheets are for the most part identical except that
the data values are different. I need to copy a chart that is on the
template sheet to the other sheets and have it pick up the same
sources, just with the new sheetname. Just copying the chart and
pasting results in a chart referring back to the template sheet.

Any Ideas where I should start?

Thanks
Robert

and this link
http://peltiertech.com/Excel/Charts/ChgSrsFmla.html
shows me what I need to know ;)

This utility is very handy, but not as powerful as it could be. Excel VBA
has some issues manipulating the series formula if the sheet name has spaces
in it, for example. One workaround, which I hope to build into the program,
is to temporarily change the sheet names to something without spaces (like A
or B), then change them back when done.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Robert H" wrote in message
oups.com...
and this link
http://peltiertech.com/Excel/Charts/ChgSrsFmla.html
shows me what I need to know ;)

I wonder if Ive made too complex of series...

If I run the utility I get an error that series ... cannot be replaced
with series...

When I try to use your tutorial code "ChangeSeriesFormula" at line:
mySrs.Formula = strTemp I get the error: Unable to set formula
property of the series class

In that case a I am replacing "Template" with "A" which are the names
of the original and new sheets that the charts reside on.

using Debug.Print just befor the error I get:
mySrs.Formula = =SERIES(Template!R2C1,(Template!R1C9,Template!
R1C11,Template!R1C13,Template!R1C15,Template!R1C17 ,Template!
R1C19,Template!R1C21,Template!R1C23,Template!R1C25 ),Template!
R2C9,Template!R2C11,Template!R2C13,Template!R2C15, Template!R2C17 ,1)
OldString = Template
NewString = A
strTemp = =SERIES(A!R2C1,(A!R1C9,A!R1C11,A!R1C13,A!R1C15,A!R 1C17,A!
R1C19,A!R1C21,A!R1C23,A!R1C25),A!R2C9,A!R2C11,A!R2 C13,A!R2C15,A!R2C17 ,
1)

interesting to see RC style references. In the source dialog box the
references are like "=Template!$A$7"

Not being able to figure out the errors and searching around, I came
up with this:

Sub MyChangeSeriesFormula()
Dim chrt As ChartObject
Dim ser As Series
Dim formulaStr 'As String
Dim oldNm As String
Dim newNm As String

oldNm = "Template"
newNm = ActiveSheet.Name

For Each chrt In ActiveSheet.ChartObjects
Debug.Print "chrt = " & chrt.Index
For Each ser In chrt.Chart.SeriesCollection
Debug.Print "ser.FormulaOld = " & ser.Formula
formulaStr = ser.Formula
formulaStr = Replace(formulaStr, oldNm, newNm)
Debug.Print "ser.FormulaNew = " & ser.Formula

Next ser

Next chrt

End Sub

No errors but "template" does not get replaced!!!!!!!!!!!!!!!! I
created a second very simple chart, copied it to the sheet and ran the
code just to make sure the complexity of the series was not a
problems. I ran into the same problem on the second chart.

Not being able to figure out the errors and searching around, I came
up with this:

Sub MyChangeSeriesFormula()
Dim chrt As ChartObject
Dim ser As Series
Dim formulaStr 'As String
Dim oldNm As String
Dim newNm As String

oldNm = "Template"
newNm = ActiveSheet.Name

For Each chrt In ActiveSheet.ChartObjects
Debug.Print "chrt = " & chrt.Index
For Each ser In chrt.Chart.SeriesCollection
Debug.Print "ser.FormulaOld = " & ser.Formula
formulaStr = ser.Formula
formulaStr = Replace(formulaStr, oldNm, newNm)
Debug.Print "ser.FormulaNew = " & ser.Formula

Next ser

Next chrt

End Sub

No errors but "template" does not get replaced!!!!!!!!!!!!!!!! I
created a second very simple chart, copied it to the sheet and ran the
code just to make sure the complexity of the series was not a
problems. I ran into the same problem on the second chart.

Did you directly output this formula?

mySrs.Formula = =SERIES(Template!R2C1,(Template!R1C9,Template!
R1C11,Template!R1C13,Template!R1C15,Template!R1C17 ,Template!
R1C19,Template!R1C21,Template!R1C23,Template!R1C25 ),Template!
R2C9,Template!R2C11,Template!R2C13,Template!R2C15, Template!R2C17 ,1)

You have some X values enclosed in parentheses, but the corresponding Y
values are not.

I have not tested my utility under R1C1 notation, nor with discontiguous X
or Y values regions. I don't know if the error lies in the utility or in the
difficulties Excel VBA sometimes has with the series formula.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Robert H" wrote in message
oups.com...
I wonder if Ive made too complex of series...

If I run the utility I get an error that series ... cannot be replaced
with series...

When I try to use your tutorial code "ChangeSeriesFormula" at line:
mySrs.Formula = strTemp I get the error: Unable to set formula
property of the series class

In that case a I am replacing "Template" with "A" which are the names
of the original and new sheets that the charts reside on.

using Debug.Print just befor the error I get:
mySrs.Formula = =SERIES(Template!R2C1,(Template!R1C9,Template!
R1C11,Template!R1C13,Template!R1C15,Template!R1C17 ,Template!
R1C19,Template!R1C21,Template!R1C23,Template!R1C25 ),Template!
R2C9,Template!R2C11,Template!R2C13,Template!R2C15, Template!R2C17 ,1)
OldString = Template
NewString = A
strTemp = =SERIES(A!R2C1,(A!R1C9,A!R1C11,A!R1C13,A!R1C15,A!R 1C17,A!
R1C19,A!R1C21,A!R1C23,A!R1C25),A!R2C9,A!R2C11,A!R2 C13,A!R2C15,A!R2C17 ,
1)

interesting to see RC style references. In the source dialog box the
references are like "=Template!$A$7"

What was printed to the Immediate Window?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Robert H" wrote in message
oups.com...
Not being able to figure out the errors and searching around, I came
up with this:

Sub MyChangeSeriesFormula()
Dim chrt As ChartObject
Dim ser As Series
Dim formulaStr 'As String
Dim oldNm As String
Dim newNm As String

oldNm = "Template"
newNm = ActiveSheet.Name

For Each chrt In ActiveSheet.ChartObjects
Debug.Print "chrt = " & chrt.Index
For Each ser In chrt.Chart.SeriesCollection
Debug.Print "ser.FormulaOld = " & ser.Formula
formulaStr = ser.Formula
formulaStr = Replace(formulaStr, oldNm, newNm)
Debug.Print "ser.FormulaNew = " & ser.Formula

Next ser

Next chrt

End Sub

No errors but "template" does not get replaced!!!!!!!!!!!!!!!! I
created a second very simple chart, copied it to the sheet and ran the
code just to make sure the complexity of the series was not a
problems. I ran into the same problem on the second chart.

Jon, thanks for the reply. I have tabled that attempt and just fixed
the data in the code so I can get a report finished. I did however,
back that up and should be back at it early next week. I will answer
your questions then.
Thanks again for the help!
Robert