macro to find and replace #ref errors with actual cell values, and stop at last sheet

[turnerben]

I have a macro I have just about managed to set up myself, to find and replace #ref errors in all worksheets in a book, with the actual cell values (in the files in which this macro will be used, columns are deleted while manual calculation is on, which causes the #ref errors in cells which referred to the deleted columns, but the cell value remains in those cells).

My only problem is that the macro generates an error at the end, when it reaches the last worksheet.

Is there a better way to set this up so that it works through every sheet and the macro terminates when it has found and replaced the last #ref error?

Here is my (amateurish) code:

Do
On Error GoTo ErrHandler:
Cells.Find(What:=".xls", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Select
Loop
ErrHandler:
ActiveSheet.Next.Select
Resume
End Sub


Thank you in advance!

[Apps]

Hi

Have you tried:

Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
'your DoLoop code goes here

Next ws