The code below is in a module and called from worksheet code. It delays a copy and paste from Sheet1 to Sheet2 by 4 seconds when triggered. However during that 4 seconds, the macro prevents data being updated on Sheet1 from an external feed, so when the copy is pasted its 4 seconds old data. Is their a way, once the Sub Copy_Race is called, to delay the execution by 4 seconds but not affect the data updates and paste the current (after 4 secs) data?
I knew Application.Now and Sleep froze the data, but was assured this method didn't?
Cheers
Code
Sub Copy_Race()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Timeout (4)
Set copySheet = Worksheets("Sheet1")
Set pasteSheet = Worksheets("Sheet2")
copySheet.Range("A1:AS21").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Sub Timeout(how_many_seconds As Double)
Starting_Time = Timer
Do
DoEvents
Loop Until (Timer - Starting_Time) >= how_many_seconds
End Sub
Display More