Making a macro that runs itself

Posted by Alexander on June 30, 2000 4:48 AM

Hello,
I am trying to design a macro that runs itself at 14:00 everyday. So, at 14:00 everyday a macro needs to copy a cell (ie A1) and paste it elsewhere as value.

Could anybody help me please?
Thank you,
Alex

Posted by Ivan Moala on June 30, 0100 2:09 PM

Alex
I believe Celia has answered this somewhere ??
BUT have a look @ this routine (I think it is
one of Chips ??) BUT look @ the online help
for Ontime

Option Explicit

Public RunWhen As Double
Public Const cRunIntervalSeconds = 10 ' secons
Public Const cRunWhat = "The_Sub"

'Use Now + TimeValue(time) to schedule something to be run when a specific amount of time
'(counting from now) has elapsed. Use TimeValue(time) to schedule something to be run at
'a specific time.eg TimeValue("21:30") runs it @ 9:30pm

Sub StartTimer()
MsgBox "Timer will start after every; " & cRunIntervalSeconds

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True

End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
End Sub

Sub StartTimer_ST()
'MsgBox "Timer will start after; " & cRunIntervalSeconds
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True

End Sub

Sub The_Sub()
'--------------------
' Put your code here'
'--------------------
StartTimer

End Sub

HTH

Ivan