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

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

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

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

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

Hi Jon!

Can you recommend a macro or formula which will execute the same
functionality as in your spiffy Chart Formula Editor (the change Sheet Name
function specifically) that could be executed on the Chart Page via button or
drop menu vs. using an Add-In. So when the charts are fed by SheetA, I
select another Sheet Name (SheetB) from a drop menu and when that name is
displayed in a cell (or after I display it and push a button) all the charts
now pull from SheetB (all ranges, series, data being the same of course)

I have posted a similar question over on the Charts thread, as I was not
sure a macro would be the best solution, as my users will have to maintain
the workbooks.

Thanks again! Cool Add-in!

:)

Greg
--
Greg


"Jon Peltier" wrote:

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