Run macro every hour

wyeah

Board Regular
Joined
Jun 23, 2003
Messages
92
Windows XP ; Excel 2007

I would like to run a macro every hour at 31 minutes past the hour.. 5:31, 6:31; 7:31. etc. THe spreadsheet stays open 95% of the time but does get closed occassionally. Below is the macro I want to run.

Thanks, Alan

Sub Update_Save()
'
' Update_Save Macro
' Update cases and save file
'
Application.CalculateFull
ActiveWorkbook.Save
End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

jc.021286

Well-known Member
Joined
Apr 12, 2010
Messages
725
okay,
I believe you need an ontime macro, try

Code:
Sub Update_Save()
'
Application.OnTime Now + TimeValue("01:00:00"), "Update_Save"

' Update_Save Macro
' Update cases and save file
'
    Application.CalculateFull
    ActiveWorkbook.Save
End Sub

Or use the same methodology to get the 31 minutes past the hour.

Regards,
jc
 

wyeah

Board Regular
Joined
Jun 23, 2003
Messages
92
Thanks jc, this will make it run every hour but how do I get it to run every hour at 31 minutes past the hour??
 

jc.021286

Well-known Member
Joined
Apr 12, 2010
Messages
725
Okay, it took me a bit to get this working as I was accidentally changing the time on my computer continuously.

Sub test()
nextruntime = TimeValue(Hour(Now) + 1 & ":31:00")
Cells(1, 1) = Time

End Sub

This gave me the desired results, thus you'll have

Application.OnTime TimeValue(Hour(Now) + 1 & ":31:00"), "Update_Save"
</pre>
Regards
jc
 

shailkam2001

New Member
Joined
Jun 3, 2014
Messages
1
Run macro every 5 Mins

Sir I want to VBA code for worksheet of excel one column as Column A1 to A10 have a any future date. this Column(A1 to A10) date compare from system date and given popup message before 10 days, 20 days..... as date mention in Column A1 is 14/06/2014 and today system date 04/06/2014 so that difference is 10 days now given the automatic popup message when open the worksheet of excel and this popup message given every 5 mins when worksheet is open, but expire 10 days as column A1 have a date is 03/06/2014 and today is system date is 04/06/2014 then difference is -1 day now given the message is expired the date with remaining 10 days message because some condition are true and some condition are false in the same worksheet of excel. please reply as early. Thanks. Shailendra Singh
 

Watch MrExcel Video

Forum statistics

Threads
1,133,325
Messages
5,658,170
Members
418,430
Latest member
Chlwls808

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top