Changing source for series collection in charts
 

I have been using Jon Peltier's Change Series formula for charts macro from
his website (thank you!), but I am having trouble getting a change string to
work when there are 2 or more series in the chart, especially with 2 y axis.
It seems that it changes the 2nd series to 1st series and makes both series
have the exact same source data, even if they are from different sheets and
the changed string for one should not have any effect on the other (meaning
they don't share the reference being changed).

The place where he makes the change is basically the following code:

''' JUST THE ACTIVE CHART
For Each mySrs In ActiveChart.SeriesCollection
mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula,
strFrom, strTo)
Next

I have tried to work with specifically
activechart.seriescollection(n).formula and I keep getting the same results.
Can anyone shed some light on how I can only change one series on the chart?

Thank you. JBL

Changing source for series collection in charts
 

It's been awhile since I worked with charts, but I seem to recall a similar
problem that I worked around by deleting all but one series, changing its
reference and then adding in other series with information regarding
secondary axis, etc.

HTH, Greg

"JbL" wrote in message
...
I have been using Jon Peltier's Change Series formula for charts macro
from
his website (thank you!), but I am having trouble getting a change string
to
work when there are 2 or more series in the chart, especially with 2 y
axis.
It seems that it changes the 2nd series to 1st series and makes both
series
have the exact same source data, even if they are from different sheets
and
the changed string for one should not have any effect on the other
(meaning
they don't share the reference being changed).

The place where he makes the change is basically the following code:

''' JUST THE ACTIVE CHART
For Each mySrs In ActiveChart.SeriesCollection
mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula,
strFrom, strTo)
Next

I have tried to work with specifically
activechart.seriescollection(n).formula and I keep getting the same
results.
Can anyone shed some light on how I can only change one series on the
chart?

Thank you. JBL