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

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" <(E-Mail Removed)> wrote in message
news:B5155F6C-2DB0-48DC-88E1-(E-Mail Removed)...
> 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