Macro With Timer Event

[Saxman]

I wish to write a macro using the normal tools which consists of a simple
copy/paste over series of events.

How can I incorporate a timer to delay the copy/paste events for something
like 30 to 60 seconds?

Is there a useful bit of code that I can obtain and paste into the code
that I will be creating or will it require something like a hidden dialogue
box with a timer on it?

 

[Dave O]

VBA has a function called Timer, a long-type integer that represents
the current number of seconds since midnight. You can use this in code
to create a delay of a number of seconds like this:

Sub Delay()
Dim Beg As Long

Beg = Timer
Do
Loop Until Timer - Beg >= 30

End Sub

This assigns the variable Beg (for Begin) with the current Timer value.
Then a DO loop starts that does nothing but loop until 30 seconds
elapses.

Does that do it for you?

 

[Saxman]

VBA has a function called Timer, a long-type integer that represents
the current number of seconds since midnight. You can use this in code
to create a delay of a number of seconds like this:

Sub Delay()
Dim Beg As Long

Beg = Timer
Do
Loop Until Timer - Beg >= 30

End Sub

This assigns the variable Beg (for Begin) with the current Timer value.
Then a DO loop starts that does nothing but loop until 30 seconds
elapses.

Does that do it for you?

I'll let you know!

Thanks.

 

[Saxman]

VBA has a function called Timer, a long-type integer that represents
the current number of seconds since midnight. You can use this in code
to create a delay of a number of seconds like this:

Sub Delay()
Dim Beg As Long

Beg = Timer
Do
Loop Until Timer - Beg >= 30

End Sub

This assigns the variable Beg (for Begin) with the current Timer value.
Then a DO loop starts that does nothing but loop until 30 seconds
elapses.

Does that do it for you?

Below is the code which gives me a compile error on the 2nd Sub Delay().
It is a simple copy from sheet 1 to sheets 2,3,4 and 5. Can you see
anything wrong. Help much appreciated.

Thanks.
......................................................................

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/12/2005 by John
'
' Keyboard Shortcut: Ctrl+m
'
Range("A1:A5").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
End Sub
Sub Delay()
Dim Beg As Long

Beg = Timer
Do
Loop Until Timer - Beg >= 30
Sheets("Sheet1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveSheet.Paste
End Sub
Sub Delay()
Dim Beg As Long

Beg = Timer
Do
Loop Until Timer - Beg >= 30
Sheets("Sheet1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet4").Select
ActiveSheet.Paste
End Sub
Sub Delay()
Dim Beg As Long

Beg = Timer
Do
Loop Until Timer - Beg >= 30
Sheets("Sheet1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet5").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End Sub

 

[Dave O]

Hi, Sax-
The reason you got an error is because you have "ambiguous" names for
your routines: you have 3 macros all named "Delay". You need to make
them unique, maybe Delay1, Delay2, and Delay3, or combine them all into
one routine called Delay, which I've done below.

Sub Delay()
Dim Beg As Long

Beg = Timer
Do
Loop Until Timer - Beg >= 30
Sheets("Sheet1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveSheet.Paste

Beg = Timer
Do
Loop Until Timer - Beg >= 30
Sheets("Sheet1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet4").Select
ActiveSheet.Paste

Beg = Timer
Do
Loop Until Timer - Beg >= 30
Sheets("Sheet1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet5").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End Sub