OnTime help needed!

pholt33

Board Regular
Joined
Jan 4, 2005
Messages
178
Office Version
  1. 2016
Platform
  1. Windows
I have a file that needs to be updated first thing every morning and then at periodic times throughout the day. What I currently have is an OnTime macro that will refresh the information every 30 minutes. Sometimes it takes longer than 30 minutes first thing in the morning to get everything done, and if the macro refreshes it messes everything up.

What I want is a macro that will refresh immediately, stop, and then start refreshing automatically at 9am. I tried using the following, but it didnt accomplish the 2nd part of what I want.

Help! Thanks!

Code:
Sub TestOnTime()

Application.OnTime Now, "macro1" 
Application.OnTime TimeValue("13:04:25"), "Macro2"
End Sub


Sub Macro1()

    Range("a1:a10").Copy
    Range("IV1").Activate
    Selection.End(xlToLeft).Select
    ActiveCell.Offset(0, 1).PasteSpecial
 '   Application.CutCopyMode
    
End Sub

Sub Macro2()

    Application.OnTime Now + TimeValue("00:00:05"), "Macro2"
    
    Range("a1:a10").Copy
    Range("IV1").Activate
    Selection.End(xlToLeft).Select
    ActiveCell.Offset(0, 1).PasteSpecial
'   Application.CutCopyMode
    
'    Application.OnTime Now + TimeValue("00:00:05")

End Sub
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
L

Legacy 98055

Guest
Run Main and see if it works. I did not test it...

<table width="100%" border="1" bgcolor="White" style="filter:progid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Private</font> NextRunTime <font color="#0000A0">As</font> <font color="#0000A0">Date</font>

  <font color="#0000A0">Sub</font> Main()
       <font color="#0000A0">Call</font> Refresh
       Application.OnTime TimeValue("09:00:00"), "ScheduleRefresh"
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Sub</font> ScheduleRefresh()
       NextRunTime = Now + TimeSerial(0, 5, 0)
       <font color="#0000A0">Call</font> Refresh
       Application.OnTime NextRunTime, "ScheduleRefresh"
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> Refresh()
       Range("a1:a10").Copy
       Range("IV1").Activate
       Selection.End(xlToLeft).Select
       ActiveCell.Offset(0, 1).PasteSpecial
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Sub</font> StopOnTime()
       Application.OnTime NextRunTime, "ScheduleRefresh", , <font color="#0000A0">False</font>
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("9202006182936484").value=document.all("9202006182936484").value.replace(/<br \/>\s\s/g,"");document.all("9202006182936484").value=document.all("9202006182936484").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("9202006182936484").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="9202006182936484" wrap="virtual">
Private NextRunTime As Date

Sub Main()
Call Refresh
Application.OnTime TimeValue("09:00:00"), "ScheduleRefresh"
End Sub

Sub ScheduleRefresh()
NextRunTime = Now + TimeSerial(0, 5, 0)
Call Refresh
Application.OnTime NextRunTime, "ScheduleRefresh"
End Sub

Private Sub Refresh()
Range("a1:a10").Copy
Range("IV1").Activate
Selection.End(xlToLeft).Select
ActiveCell.Offset(0, 1).PasteSpecial
End Sub

Sub StopOnTime()
Application.OnTime NextRunTime, "ScheduleRefresh", , False
End Sub</textarea>
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,834
Messages
5,855,905
Members
431,772
Latest member
dannyboi1

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