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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,228
Messages
5,509,923
Members
408,764
Latest member
Abdul Matin

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top