Schedule time to run macro

[kenmunro]

I'm trying to set a macro to run at a set time each day.

I recorded the macro and it is named Macro1

I clicked into View Code and entered the following -

Private Sub Workbook_Open()
Application.OnTime TimeValue("15:15:00"), "Macro1"

End Sub

Then i went to Module1 and entered -

Sub Macro1()
Application.OnTime TimeValue("15:15:00"), "Macro1"
'
' Macro1 Macro
'

'

Sheets("Sheet1").Select
Columns("A:A").Select
Selection.Copy
Sheets("IMPORT SHEET").Select
Columns("A:A").Select
ActiveSheet.Paste
Range("B3").Select
End Sub

Nothing happens! (I do have the workbook open)

Can anyone help?

[blane245]

This is not triggering unless you open the workbook. You can start it is by running the Workbook_Open sub while the workbook is open.

Another way to do this is to set up a Windows task that will open the workbook on your shedule. Then the Workbook_Open sub can run the macro.

[kenmunro]

Thanks Bob,

I tried this but again it failed to run - am i doing something wrong?

Private Sub Workbook_Open()
Application.OnTime TimeValue("09:20:00"), "Macro1"
Run "Macro1"

End Sub

[blane245]

I'm not sure exactly why this is not working. Here's something to use for testing that will run the macro every minute. Be sure to follow the instructions.

Put the following code in the TheWorkBook object of the Excel workbook you want to run. It will start runing Macro1 one minute after you start it.

Please Login or Register  to view this content.

Put the following code in the Module1 object of the same Workbook. It will restart itself every minute after it runs

Please Login or Register  to view this content.

Save the Workbook, then close and reopen it. The message should appear every minute. You can stop it by putting a breakpoint in Macro1 at the first line and select Stop in the debugger.

This will only run as long as the workbook is open.