Macro to copy & paste values for every cell in the workbook containing a specific formula

Hi all I have created the following macro, which effectively finds and replaces any specified formula, but I can't seem to get it to loop through every sheet in the workbook. Although i'm familiar with the common sheet loop Dim sh As Worksheet
For Each sh In Worksheets
'do something here
msgbox sh.Name
Next sh

In my code you won't find any effort to loop through the sheets, because all attempts have failed. See code below:

Thanks for your help!

Sub AB()
Dim ws As Worksheet
Dim WhatToFind As Variant
Dim iCtr As Long


WhatToFind = Array("round", "sumif", "sumifs", "vlookup", "sumproduct", "today")
For iCtr = LBound(WhatToFind) To UBound(WhatToFind)
With rng

Set rng = Cells.Find(What:=WhatToFind(iCtr), _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rng Is Nothing Then
Do
rng.Formula = rng.Value
Set rng = Cells.FindNext(rng)
Loop Until rng Is Nothing

End If
End With
Next
End Sub

Perhaps like this?

Please Login or Register  to view this content.

Alf