Run a Macro on the 1st of every month at 7AM

[JWilson9608]

I have a workbook that is set up to always stay open with a Private Sub Workbook_BeforeClose function. I have written a macro called Save_And_Clear to perform a number of routines on the 1^st of the month at 7AM. The Save_And_Clear macro works great but I’m having a hard time figuring out the code for the macro to be called on the 1^st of every month at 7AM. A currently have an Application.OnTime Day(Date) = 1, “Save_And_Clear” to run the macro but I’m not sure what will happen on the NEXT 1^st of the month because the workbook is not allowed to close. I am also having trouble with the 7AM part of the Sub. In testing, I can make it run on startup by date and I can make it run on startup by time Application.OnTime TiveValue(“07:00:00”), “Save_And_Clear” but I can’t seem to make it run by date AND time. Any help would be greatly appreciated.

 

[shg]

I think you'd be better off running the workbook from Windows Task Scheduler, but ...

Option Explicit

Dim Runtime As Date

Sub Sched()
  If Runtime = 0 Then
    Runtime = DateSerial(Year(Date), Month(Date) + 1, 1) + CDate("7:00")
  Else
    Call myMacro
    Runtime = DateAdd("m", 1, Runtime)
  End If
  Application.OnTime Runtime, "Sched"
End Sub

Sub myMacro()
  ' do stuff
End Sub

 

[JWilson9608]

Thanks for the response. If it would be better in Windows Task Scheduler is that something you could help me with

 

[shg]

I don't use it often enough to tell you off the top of my head, but basically you schedule the workbook to open with the desired recurrence, and code so the macro is run from the Workbook_Open event.