Normal Search & replace will let you replace the workbook references in
formulae, but not in the chart series. You need to do it in the macro code -
but I'm assuming that's not a problem for you since you've posted in an Excel
Programming newsgroup and you've said you've got a macro already.

I'm using Excel 2003. VBA probably hasn't changed a lot since 97, but
there's a chance my code won't work.
This is a complete sub - I suggest you grab the bits you want and add to
your existing code. You also need to change the filenames (just under the dim
statements) to match yours. If you already have variables for these in your
code, then replace my oldBook and newBook variables with the variables you've
already got.
I'm not sure, but I think if there are formulae in the chart titles then
they do not get "fixed", but it should work on the chart series.
-------------------------------------------
Sub RemoveLinks()
'
' RemoveLinks Macro
' Cringing Dragon 21/10/2007
'
Dim oSheet As Worksheet
Dim oChart As Object
Dim oSeries As Series
Dim newBook As String
Dim oldBook As String

' Replace these with YOUR workbook names
newBook = ActiveWorkbook.Name
oldBook = "Book2"

' Turn error checking off so it doesn't stop if any of the values can't be
changed
On Error Resume Next

' This loop checks all worksheets
For Each oSheet In Workbooks(newBook).Worksheets
' Remove links in cell formulae
oSheet.Cells.Replace What:="[" & oldBook & "]", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
' This loop checks all embedded charts on the sheet
For Each oChart In oSheet.ChartObjects
' This loop checks all series in the chart
For Each oSeries In oChart.Chart.SeriesCollection
oSeries.Formula = Replace(oSeries.Formula, "[" & oldBook & "]",
"")
Next oSeries
Next oChart
Next oSheet

' This loop checks all chart sheets
For Each oChart In Workbooks(newBook).Charts
' This loop checks all series in the chart
For Each oSeries In oChart.SeriesCollection
oSeries.Formula = Replace(oSeries.Formula, "[" & oldBook & "]", "")
Next oSeries
Next oChart

' Turn normal error handling back on
On Error GoTo 0

End Sub
-------------------------------------------


"Fan924" wrote:

Excel 97. Macro copies data and a dozen charts to a new workbook.
Charts still linked to old book. Can I change book and sheet to the
new sheet automatically? Search and replace?