Running a macro automatically at the same time everyday

[kurtc]

I am trying to write a macro that will save the data in one of the worksheets of a workbook everyday at 11:58 pm. Here is what I have done, It only runs one time and never again. Any help would be appreciated.

Private Sub Workbook_Open()
Application.OnTime TimeValue("23:58:00"), "Macro1"
End Sub

Private Sub workbook_after_save()
Application.OnTime TimeValue("23:58:00"), "Macro1"
End Sub

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/5/2012 by kurtc
'

Sheets("Results").Select
Cells.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="H:\ME\Databases\F Series Test Stand\Test Results_" & Format(Date, "ddmmmyyyy") & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close
Rows("2:1000").Select
Selection.ClearContents
Range("A2").Select
ActiveWorkbook.Save
End Sub

[StevenM]

I haven't tested this, but how about adding something like this at the end of your macro:

Please Login or Register  to view this content.

If it was me, I would change the time so that it is not so close to before midnight. If for some reason your macro took over 2 minutes to run, it would set the time for the day after and not the next day.

[kurtc]

Other then waiting for 2 days to pass is there a way that I can test this easily?

[kurtc]

As far as I can tell this is not working. When you say at the end of my macro, where exacly do you mean?

[john55]

the code provided by Steven it works.
try to test it now

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

[kurtc]

But will it run everyday at the same time?

[StevenM]

I've never tested it, but I will make one observation. Your original Application.OnTime only set the time when the macro should run. My suggestion was to set both the day and the time. "Date + 1" is just another way of saying "tomorrow". Thus Date + 1 + TimeSerial(23, 58, 0) is tomorrow at: 23:58. Because 23:58 is so close to 24:00/00:00, I would recommend moving the time back to 23:30 or some such. Let us know if it works.

[kurtc]

I did what you said and will be trying it over the weekend. Should have an answer for you on Monday. I placed this in the ThisWorkbook of the project

Private Sub Workbook_Open()
Application.OnTime Date + 1 + TimeSerial(23, 45, 0), "SaveResults"
End Sub

and then the SaveResults Macro in a module.

Sub SaveResults()
'
' SaveResults Macro
' Macro recorded 4/5/2012 by kurtc
'

Sheets("Results").Select
Cells.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="H:\ME\Databases\F Series Test Stand\Test Results_" & Format(Date, "ddmmmyyyy") & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close
Rows("2:1000").Select
Selection.ClearContents
Range("A2").Select
ActiveWorkbook.Save
End Sub

[StevenM]

FWIW ... I would have used your original idea.
This is for today.

Please Login or Register  to view this content.

Then at the end of I would add:

Please Login or Register  to view this content.

This addition would be for tomorrow and the day after.