I've written this different before, but I can't remember what I did to speed
it up. Also, I would like it to look at the entire sheet but only the cells
that have a formula so you don't have to select a range...It's too slow to
be practical right now. Btw the code is finding all the vlookup's and
copypastespecial the values...

Sub copypastevlookup()
On Error GoTo whoops
Application.ScreenUpdating = False

For Each Cell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
a = a + 1
Next

What = "vlookup"

For Each cl In Selection.Cells

Set Cell = Cells.Find(What)
If Not Cell Is Nothing Then
'! found!
' 'ws.Activate
Cell.Select
Cell.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
' Exit For
ElseIf Cell Is Nothing Then
Exit For
End If
'Debug.Print cl.Address
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True

whoops:
If Err.Number = "1004" Then
MsgBox "There are no formulas in worksheet"
Exit Sub
End If

End Sub


Any comments appreciated!