ActiveCell.PasteSpecial (xlPasteValues) hangs program
I have a worksheet with formula that auto-eneters the date (NOW()),
based on the value of another cell. The cell is either todays date, or
an emopty string.
==IF(I26=0,NOW(),"")
When exiting the sheet I want to convert any dates entered to thier
literal value (i.e remove the formula) so that the date is not
overwritten with a future date when the sheet is reopened.
This code works, but takes a very long time , and when stepping
through I see it hangs for several seconds on each call to
pastespecial, making it take over 10 minutes for 3000 rows.
Any reason for this - is thier a faster way to achieve the same
result?
thanks,hals_left
For Each Cell In Worksheets(1).Range("myRange")
If IsDate(Cell.Value) Then
Cell.Select
Cell.Copy
ActiveCell.PasteSpecial (xlPasteValues)
End If
Next
|