OnTime help needed!

[pholt33]

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!

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

 

[Legacy 98055]

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

<table width="100%" border="1" bgcolor="White" style="filterrogid: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>