OnTime help needed!

pholt33

Board Regular
Joined
Jan 4, 2005
Messages
164
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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,118,701
Messages
5,573,704
Members
412,548
Latest member
wallisonlac
Top