Hi,

Choose Edit, Links, and Change Source to your current workbook. May be able
to dispense with the macro using this technique.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"usmc-r70" wrote:

I have a workbook with a 'template' worksheet that has 4 charts that
reference data contained only in this worksheet .

When I copy the 'template' and rename it (say Project1), the 'Series
Values', 'Series Name', and 'Title' reference in the formula bar continue
referencing the 'template' worksheet.

I found the code shown below and it works for chart 'Series Values', 'Series
Name', but NOT the chart 'Title' that I have referenced in the formula bar.
Credit to: http://peltiertech.com/Excel/Charts/...xzz0KCalAl7L&D

How can I modify the code below to:

1. Include the 'Title' reference shown in the formula bar.

2. Instead of typing the new worksheet name into the dialog box, have the
code reference $AC$37 which has the formula: =MID(CELL("filename",B31),
FIND("]",CELL("filename",B31))+1,255).

Sub ChangeSeriesFormulaAllCharts()
''' Do all charts in sheet
Dim oChart As ChartObject
Dim OldString As String, NewString As String
Dim mySrs As Series

OldString = InputBox("Enter the string to be replaced:", "Enter old
string")

If Len(OldString) 1 Then
NewString = InputBox("Enter the string to replace " & """" _
& OldString & """:", "Enter new string")
For Each oChart In ActiveSheet.ChartObjects
For Each mySrs In oChart.Chart.SeriesCollection
mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula,
OldString, NewString)
Next
Next
Else
MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
End If
End Sub


http://peltiertech.com/Excel/Charts/...xzz0KCalAl7L&D