remove erroneous references

Hi, I need some help with a macro.

I have a template, but sometimes I don't need some sheets and I have to delete them. This converts some formulas like:

sheet1!A1+sheet2!A1+sheet3!A1

into

sheet1!A1+#¡REF!A1+sheet3!A1

I recorded a macro with "search and replace" to delete the "#¡REF" links, and I get this code, but It doesn't work if I run it directly as a macro.

Cells.Replace What:="+#¡REF!????+", Replacement:="+", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Cells.Replace What:="+#¡REF!???+", Replacement:="+", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Cells.Replace What:="+#¡REF!????", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Cells.Replace What:="+#¡REF!???", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


I dunno if the '?' symbols doesn't work in the macro code, or if something else is needed to get a functional macro.

Any suggestion?

X-O

How is your template being built? It seems logical to address the Sheet issue at that point is the process. If you must remove REF each time, something is wrong.

The template is build with tables in several sheets. We capture info of each element in each sheet. the 3 last sheets summarize the info of all elements. According to the number of elements, sometimes we don´t use all sheets.

The reason to use the template and delete the unused sheets, is that the final sheets have formulas of the type:

sheet1!A1+sheet2!A1+sheet3!A1

in each cell of the tables (about 100 formulas in each table).

So, I think is more easy to replace the 100 references to the deleted sheets, than rebuild all the formulas in the final sheets.

So, the macro should replace all the references in 2 or 3 sheets.

Thinking about the active sheet when I run the macro, I get the same null effect if I am in the sheet with the erroneous references.

I tried to 'expand' the effect adding the next code at the beginning of the macro:

Dim Libro As Workbook
Dim Hoja As Worksheet
Set Libro = Application.ActiveWorkbook

On Error Resume Next

For Each Hoja In Libro.Worksheets
...


and the rest of the previous code.

I don't know a lot of programming, but I think this code should 'walk' across each sheet and make the replacement.

But the result is the same. Nothing happens.

For formulas which are of type:
=sheet1!A1+sheet2!A1+sheet3!A1

A trick you can use is to create two "buffer" sheets.
Example:

Create a new workbook and give it 6 worksheets, ordered from left to right Sheet1 --> Sheet6.
On Sheet1 (your "summary" sheet), put this formula in cell A1:
=SUM(Sheet2:Sheet6!A1)
Then hide Sheet2 and Sheet6 (these are the "buffer" sheets).

You can then test your template - deleting sheets will not result in #REF! errors.


If this sort of solution is not viable, then sure we can help you fix your code.