Formula's don't link after a file name changes

[DPelch]

Thanks for the advice. What would you suggest to do when you hav
several (close to 40) spreadsheets and each one has multiple formula
in it?

Would a Ctrl+H work on all the formulas in the spreadsheet at once, o
do I need to do it cell by cell?

Thanks,
Da

 

[Bernie Deitrick]

Dan,

It will work on all the cells that you select, if you select more that one
cell, or on all cells if you only select one cell (go figure). If you want
to be sure, you can select all the cells by clicking on the button the upper
left of cell A1 or by using Ctrl-A (though Ctrl-A may not work in XL2004).

If you have the files all in one folder, you should be able to change all of
them by using the macro below, changing the text strings as needed. As
written, the macro will only look at the active sheet, but can easily be
changed to do all sheets.

HTH,
Bernie
MS Excel MVP

Sub RunMacroOnAllFilesInFolder()
With Application.FileSearch
.NewSearch
'Change the folder name
.LookIn = "C:\Excel"
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
'Put your recurring macro code here
Cells.Replace What:="FindThis", _
Replacement:="Replacement", _
LookAt:=xlPart
ActiveWorkbook.Close True
Next i
End If
End With
End Sub