Any takers?
 

Looking to speed up the code below..here is the post from the previous
conversation...


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!

Any takers?
 

Please stay in the original thread for assistance.

--
Don Guillett
SalesAid Software

"Job" wrote in message
...
Looking to speed up the code below..here is the post from the previous
conversation...


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!