Pause vba for 20 seconds

[ste33uka]

I have the following vba code that runs every 5 minutes
Would it be possible to pause it for 20seconds after the line
.Range("BO2:CC2").Value = Application.Transpose(.Range("O5:O19").Value)
And that excel would not be frozen?
Thanks

Sub test()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
   

    With Sheet1
        .Range("BO2:CC2").Value = Application.Transpose(.Range("O5:O19").Value)
    End With
    With Sheet2
        .Range("BO2:CC2").Value = Application.Transpose(.Range("O5:O19").Value)
    End With
    With Sheet3
        .Range("BO2:CC2").Value = Application.Transpose(.Range("O5:O19").Value)

       
    With Sheet1
        .Range("BO8:CC8").Value = Application.Transpose(.Range("O5:O19").Value)
    End With
    With Sheet2
        .Range("BO8:CC8").Value = Application.Transpose(.Range("O5:O19").Value)
    End With
    With Sheet3
        .Range("BO8:CC8").Value = Application.Transpose(.Range("O5:O19").Value)
  
  

    Application.OnTime Now + TimeValue("00:05:00"), "test"

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

 

[John_w]

That is a harsh/misleading statement.

You make it sound like it 'breaks' the script.

In reality, after the 'Wait' time has elapsed, Excel execution continues normally.

It's not misleading at all. Lock/freeze doesn't mean 'break' the script; they mean the Excel UI is unresponsive. And your line of correct code won't break the script.

The OP asked for a pause which won't freeze Excel and an Application.Wait for 20 seconds will lock/freeze Excel. The crucial thing is the DoEvents, which allows Windows to process mouse and keyboard events, and to set Application.ScreenUpdating = True, which should allow you to select/scroll sheets/cells, enter cell data, etc. whilst the pause loop is running.

 

[John_w]

Or just using a Date variable :​

    Dim D As Date
    D = Now + TimeValue("0:0:20")
    While D > Now:  DoEvents:  Wend

Yes, much simpler!