Running Macros at timed intervals

Allan

Try to do what you want by putting the following in a module.
To test it, you can change the TimeValue from “04:00:00” (4 hours) to “00:00:05” (5 seconds)

Dim nextRun As Date
Option Explicit

Sub StartMacro()
'Run this macro to start "YourMacro"
Call ReRun
End Sub

Sub ReRun()
'This automatically runs "YourMacro" every 4 hours
nextRun = Now + TimeValue("04:00:00")
Application.OnTime nextRun, "ReRun"
Call YourMacro
End Sub

Sub YourMacro()
'PUT YOUR CODE HERE
End Sub

Sub Auto_Close()
'Turns off the OnTime event when closing the file
Call StopMacro
End Sub

Sub StopMacro()
'Run this to turn off the OnTime event
On Error Resume Next
Application.OnTime nextRun, "ReRun", schedule:=False
End Sub

Celia

Allan

This message could not be delivered to the address you supplied, so I'm posting it here :-

Replace “YourMacro” (in the two places it appears) with “Macro1”.

The five macros have to be in the same workbook. They can still work if they are in different workbooks, but the code would have to be changed.

To start the automatic running of Macro1 every 4 hours, the macro named StartMacro has to be run first (after opening the workbook). Instead, if you want the auto-run process to start automatically as soon as the workbook is opened, change the macro named StartMacro to Auto_Open.

An explanation of each macro is :-

StartMacro - Run this to kick-start the auto-run process

ReRun - This sets the OnTime event to run Macro1 every 4 hours

Macro1 - This is your macro that you want to run every 4 hours

Auto_Close - Turns off the OnTime event (otherwise the workbook will be opened every 4 hours)

StopMacro - Run this if you want to keep the workbook open but want to switch off the automatic updating. You can re-instate the auto-updating by running StartMacro

If you are still unable to get it working, mail me your workbook so I can check what’s wrong.

Celia

cc

I tried this and I'm getting an error message.
I can run the Start Macro and the macro runs ok and it also runs when I start the rerun macro with a button click but when it comes time to have it run automatically it comes up with an error message, "Abiguous file: Rerun"

Does this makes sense?

thanks,

Allan