Automatic Macros
Posted by Mark Edwards on February 11, 2002 8:06 AM
HI,
I have sucessfully written a macro to create a new sheet with todays date as the name and fill it with relevant data.
What i need to be able to do is run this macro at 08.00 everyday, The workbook is always open, there is only one sheet on view (the rest are veryhidden).
How can i do this?
Thanks In Advance.
Posted by Damon Ostrander on February 11, 2002 1:10 PM
Hello Mark,
Hi Mark,
You can do this using the OnTime method of the
Application object. Use OnTime to schedule the
first time the macro runs:
Application.OnTime Date() + 1 + TimeValue("8:00:00"), "MyMacro"
to get the macro named "MyMacro" to run tomorrow
at 8:00 a.m. One is added to today's date to get to
0 hours tomorrow. Then also add this line of code to
the MyMacro macro so that it will re-schedule itself
to run again the next day. Each time it runs it
re-schedules itself for the following day.
You should probably also include this code in the
Workbook_Open event so that if you close and re-open
the workbook it will re-schedule itself, since any
OnTime scheduled event is lost when you close Excel.
Happy computing.
Damon
PS. You can test this macro by setting it to run
at a particular time (e.g., 3:05:15) today:
Application.OnTime Date() + TimeValue("15:05:15"), "MyMacro"
