Sheet names with special characters, including spaces and hyphens, need to
be enclosed in single quotes:

'My Sheet'
'Your-Sheet'

I have a Chart Formula Editor on my web site that can change these aspects
of a series formula. When you enter your sheet names in the Change From and
Change To fields, it the sheet name needs single quotes, enter them around
the sheet name, and the editor will work.

http://peltiertech.com/Excel/Charts/ChgSrsFmla.html

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


"Al" wrote in message
...
Tom,

That's a nice solution for copying a whole sheet of data and charts, and
changing the copied chart data source to the new sheet. It helped me do
just
that, but I found an unexpected limitation. If the sheet name is
hyphenated,
the <ser.Formula=s1 line seems to interpret it as a minus sign and the
macro
halts at that line. Renaming the sheet is the obvious fix.
--
Al C


"Zilla" wrote:

Thanks Tom, that worked a treat!

"Tom Ogilvy" wrote:

This worked for a line chart:

Sub G()
Dim ch As Chart
Dim s As String, s1 As String
Dim ser As Series
Set ch = ActiveChart
For Each ser In ch.SeriesCollection
s = ser.Formula
s1 = Application.Substitute(s, "Sheet1", "Sheet2")
ser.Formula = s1
Next
End Sub

--
Regards,
Tom Ogilvy


"Zilla" wrote in message
...
Hi,

I need a one time solution to this problem, does'nt have to be
elegant or
repeatable!:
I need to be able to change the sheet part of the chart source
property
whilst not touching the cell references, which are currently correct.

My ideal solution is: to have a macro open up the chart and perform a
'find
and replace' action on the sheet source, leaving the whoel rest of
the
string
alone.


I am new to programming excel (though have a little exp in Access),
so if
you DO have any suggestions please make them self explanatory!


Thanks in advance