CODEWITHSUNDEEP
vbaexcel
lordf
lordf

Reputation: 70

Application.ontime code isn't running

I've done a fair bit of research on here to try and get this code to run, but I'm still having issues.

I am trying to run a macro at a set time. In fact it is a number of macros as different times. This macro then copies a block of cells, into a different area on the sheet.

I will not be able to use windows scheduler so I'm trying to do it this way. I gather without scheduler I will have to run the macro from a button. Therefore, my first sub is this (note button is on a separate sheet to the data)

Sub Call_save()

    Call Sheets("Dashboard").8amsave

    Application.OnTime TimeValue("08:10:00"), "Call_8amsave"

    Call 9amsave

    Application.OnTime TimeValue("09:10:00"), "Call_9amsave"

    Call 10amsave

    Application.OnTime TimeValue("10:10:00"), "Call_10amsave"

End Sub

This button is designed to then run the subs at the predetermined time. The subs it calls are these: 

Sub 8amsave()
Dim current_data As Variant

    current_data = Worksheets("Dashboard").Range("S6:V22").Value
    Worksheets("Dashboard").Range("B33:E49").Value = current_data


End Sub

Sub 9amsave()
Dim current_data As Variant

    current_data = Worksheets("Dashboard").Range("S6:V22").Value
    Worksheets("Dashboard").Range("B54:E70").Value = current_data


End Sub

Sub 10amsave()
Dim current_data As Variant

    current_data = Worksheets("Dashboard").Range("S6:V22").Value
    Worksheets("Dashboard").Range("B75:E91").Value = current_data


End Sub

Now, when I run the initial button it runs all the subs at once and copies the sells into the correct places, straight away. This isnt really an issue for me. However, at the predetermined time I get an error saying unable to run the macro... can't find it or sheet unable to run macros... or something of that ilk.

Is there anything blindingly obvious that i am missing? I have kind of been working this out as I go along so my VBA knowledge is fairly limited. A point in the right direction would be great.

Upvotes: 0

Views: 3354

Answers (1)

David Zemens
David Zemens

Reputation: 53623

Your OnTime statements also appear to be referencing procedure names which don't exist, i.e., Call_8amSave as opposed to 8amsave. Note also that procedure names cannot begin with numeric (e.g., 8amsave) and this code should not even compile, let alone execute.

Rename your procedures so that they match the calls in the OnTime statement, and are legally permissible procedure names, like: Sub Call_8amSave, etc.

Also, the OnTime property can only have one procedure at a time, so you can't set 3 future times, you have to set them sequentially. So, set the OnTime for the 9am save during the 8am procedure, etc.

Option Explicit

Sub Call_save()

    Application.OnTime TimeValue("08:10:00"), "Call_8amsave"

End Sub
Sub Call_8amsave()

    With Worksheets("Dashboard")
        .Range("B33:E49").Value = .Range("S6:V22").Value
    End With

    Application.OnTime TimeValue("09:10:00"), "Call_9amsave"
End Sub

Sub Call_9amsave()

    With Worksheets("Dashboard")
        .Range("B54:E70").Value = .Range("S6:V22").Value
    End With

    Application.OnTime TimeValue("10:10:00"), "Call_10amsave"
End Sub

Sub Call_10amsave()

    With Worksheets("Dashboard")
        .Range("B75:E91").Value = .Range("S6:V22").Value
    End With


End Sub

Upvotes: 1

Related Questions