Run Macros at Specific Date and Time

[AV_Geek]

I have a series of Macros that I'm looking to run automatically at about 2:00am. I will run 3 macros every day, however which three and the order will change every day. The entire workbook is used for 31 days.

What I'd like to do is add a sheet called "Schedule" that will look something like the attached file, that will basically call that day's 2:00am macro at 2:00am, the 2:30am macro at 2:30am, and the 3:00am Macro at 3:00am. I would manually fill in the green portions prior to the first of every month and change the dates every month.

Also, please forgive me on this, but I can't seem to see how to attach a sample workbook; I can only attach an image.

 

[Micron]

In case you don't get a code solution I'll suggest that you look into Application.OnTime . I've dabbled with it only to help out others with code, but I've never tried to run different multiple code blocks with it, and not for Mac either. Should be doable, but your wb will have to remain open I think, otherwise you may need a helper cell to store what ran last. That's because any variable you use for this would reset next time the wb opens, which would start the cycle at the beginning again, no matter where it was in the process before that.

If this was about Access, I'd suggest Task Scheduler because you can pass a Command property value that an opening db can use to make decisions in code and run that according to a schedule. Don't think you can do that in Excel though.

 

[Micron]

Forgot to mention that you cannot upload files to this forum. A drop box type of link is about your only option.

 

[AV_Geek]

I spent half of the day on this, and I came up with

Sub A()

Application.OnTime DateValue("2024,11,6") + TimeValue("17:16:00"), Procedure:="Download_Loads_Onedrive.DL_Weekdays"

End Sub

This works for a fixed date, time, and macro. The next part is how to change the DateValue, TimeValue, and Module.Macro to variables.
Any help is appreciated.

 

[Logit]

The below information will assist you entering the correct times in Row 3 :

1 am to 12:59 pm … enter as : 1:10, 7:30, 12:32
1 pm to 11:59 pm … enter as 13:10, 19:30, 23:32
12 midnight to 12:59 … enter as 00:10, 00:30, 00:32
I tried using A, B, C as reference to the three macros (B4:D4). Excel will not accept
"C" as a macro name. "C" must be a reserved variable used in Excel. This is the
reason you must enter the macro names as AA, BB or CC.

Here is the complete code you are seeking :

Option Explicit

Sub SelectCurrentDateTimeCell()
    Dim ws As Worksheet
    Dim currentDate As Date
    Dim currentTime As String
    Dim dateCell As Range
    Dim timeCell As Range
    Dim intersectCell As Range
    Dim x As String
    Dim wbTarget As Workbook

    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
    currentDate = Date
    currentTime = Format(Time, "hh:mm:ss") ' Adjust format as needed

    ' Search for current date in Column A
    Set dateCell = ws.Columns("A").Find(What:=currentDate, LookIn:=xlValues, LookAt:=xlWhole)

    ' Search for current time in Row 3
    Set timeCell = ws.Rows(3).Find(What:=currentTime, LookIn:=xlValues, LookAt:=xlWhole)
    
    Set wbTarget = ThisWorkbook
Range("H2").Value = Now
    ' If both date and time are found, select the intersecting cell
    If Not dateCell Is Nothing And Not timeCell Is Nothing Then
        Set intersectCell = ws.Cells(dateCell.Row, timeCell.Column)
        intersectCell.Select
        'MsgBox intersectCell.Value
        x = intersectCell.Value
        Application.Run intersectCell.Value
    End If
    ScheduleCopyPriceOver
End Sub

Sub auto_open()
    Call ScheduleCopyPriceOver
End Sub

Sub ScheduleCopyPriceOver()
    Dim TimeToRun
    TimeToRun = Now + TimeValue("00:00:01")
    Application.OnTime TimeToRun, "SelectCurrentDateTimeCell"
End Sub

Sub CopyPriceOver()
    Calculate
    SelectCurrentDateTimeCell
End Sub

Sub auto_close()
    Dim TimeToRun
    On Error Resume Next
    Application.OnTime Now + TimeValue("00:00:01"), "SelectCurrentDateTimeCell", , False
    MsgBox "Stopped"
End Sub

Sub AA()
    MsgBox "You chose A"
End Sub

Sub BB()
    MsgBox "You chose B"
End Sub

Sub CC()
    MsgBox "You chose C"
End Sub

You can download the example workbook here : Internxt Drive – Private & Secure Cloud Storage

 

[AV_Geek]

Thanks - I'll try and look at it later tonight or over the weekend.