Global replace a reference to a worksheet

[dhstein]

This is a long story, but I'll give the crux of the issue. We have a large
workbook with many worksheets. Most of the worksheets have hundreds of
formulas that refer to a worksheet called "Sales". There is a macro that
runs and updates "Sales" from another file. Something isn't working right
now - and it seems to be a corrupt "Sales" worksheet. In any case, we have a
backup of the file and we can use the "Sales" tab from the backup. But there
is a lot of new data in the other sheets that we'd like to keep. I've been
able to rename "Sales" to "Sales.bad" and bring in the sheet Sales that runs
the macro perfectly. The problem is changing all the formula references from
Sales.bad (renaming the sheet changed them all) to "Sales". Is there some
VBA routine that I could implement to edit each formula in a worksheet and
replace "Sales.bad! ........." with "Sales! ....." Thanks for any help
on this.

 

[Sheeloo]

So you want to change all the formulas?

One way is to search and replace = with something like ^^^=
Then search for sales.bad and replace with sales
Finally replace ^^^= with =

In the options choose Within workbook