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!

Are the only formulas in the spreadsheet vlookup formulas? If so then it
would obviously make more sense simply to copy the entire sheet/range and
paste special as values.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Job" wrote in message
...
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!

Nope, that's the point...I want to keep all formula's except the vlookups...
"Ken Wright" wrote in message
...
Are the only formulas in the spreadsheet vlookup formulas? If so then it
would obviously make more sense simply to copy the entire sheet/range and
paste special as values.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Job" wrote in message
...
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!

Anyone...


"Job" wrote in message
...
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!