Copying and pasting rows (1 Viewer)

[Blaster]

I have a sheet in excel with formulas, after some time I would like to transform some rows in values. Can anyone help me?

Eg.

row 1:1 copy and paste values,
then 5 min after,
row 2:2 copy and paste values,
.
.
.
then 5 min after,
row 1000:1000 copy and paste values

thank you

 

[whitespace]

Something along the lines of:

Sub waitCopyAndPaste()
Const StartRow As Long = 1
Const EndRow As Long = 1000

Dim x As Long

For x = StartRow To EndRow
    Rows(x & ":" & x).Select
    Selection.Copy
    Application.Wait (Now + TimeValue("0:05:00")) ' Wait 5 minutes
    Selection.PasteSpecial Paste:=xlPasteValues
Next x

End Sub

However I would obviously change the time to timevalue("0:00:01") to wait for one second whilst you try the sub out and make sure it works before switching back to 5 mins.

Hope this helps

 

[Blaster]

Hi, thanks for the code, it works, but between the first copy and past and the second copy and past the excel is frezeed, I cannot do anything. Is any way to stop frezeeing the excel?

Thanks a lot

 

[whitespace]

Ahh, to be honest I'm not sure if you can do that in Excel VBA. I know in 'proper' programming languages such as Java and C# you can set up programs to run as threads that could possibly get around this - but I haven't a clue how to do this in Excel VBA or even if it's possible - i would suspect not. I thought you might be able to use DoEvents but had a quick play with this and don't think this will work for you.

Sorry, don't know if anyone else has any ideas.

 

[chergh]

You could put the timer code in a different workbook and use that workbook to call the copy and paste sub in the workbook with the data. This should allow you to work with the other workbook. If you use this approach you will need to avoid using the select method and selection object.