Copying and pasting rows

[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

[stanleydgromjr]

Blaster,

Try:

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub Test()
    
    'Sleep 1000  ' to make your macro "sleep" for one second ( 1000 milliseconds )
    '1 second    1000    milliseconds
    '1 minute    60000   milliseconds
    '5 minutes   300000  milliseconds

    With ActiveSheet
        With Rows("1:1")
            .Copy
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With

        Sleep 300000
        With Rows("2:2")
            .Copy
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With

        Sleep 300000
        With Rows("1000:1000")
            .Copy
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With
    End With

End Sub

Then run the "Test" macro.


Have a great day,
Stan

[Blaster]

Hi,

It worked, but I wanted to copy and paste all the rows between 1 and 1000, and not only the three rows that I said. Thank you again.

[SOS]

Hi Blaster,

What about

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub Test()
Dim xRow As Long
    
    'Sleep 1000  ' to make your macro "sleep" for one second ( 1000 milliseconds )
    '1 second    1000    milliseconds
    '1 minute    60000   milliseconds
    '5 minutes   300000  milliseconds
For xRow = 1 To 1000
    With ActiveSheet
        With Rows(xRow & ":" & xRow)
            .Copy
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With
Sleep 30000
    End With
Next xRow
End Sub

[Blaster]

That code works almost perfectly. But it freezes the excel between each copy and paste.

I searched in the forum and found a site with an example with I tried to use.

Example

This example pauses a running macro until 6:23 P.M. today.

Application.Wait "18:23:00"
		

This example pauses a running macro for approximately 10 seconds.

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
		

This example displays a message indicating whether 10 seconds have passed.

If Application.Wait(Now + TimeValue("0:00:10")) Then
    MsgBox "Time expired"
End If

But this code also freezed the excel. Is there a way to run the macro without freezing?

Thx