Another timed macro question

Posted by Darci on May 21, 2001 1:48 PM

i have another question as a follow up to #17867
i am trying to have a macro start at 10am, and it hasn't worked thus far. I cannot get the code to run the macro. I haven't gotten any error messages, but it doesn't do anything. Here is the code i am using...
(in the general module)
Sub StartTimer() Sub Macro1() ' ' Macro1 Macro ' 10am price cut&paste values ' ' Application.Run "RefireBLP" Range("T4:T17").Select Selection.Copy Range("U4:U17").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ActiveWorkbook.Save Range("X8").Select Application.OnTime TimeValue("09:58:00"), "StartTimer" i need the values at 10am, does the 5min interval in you 11.55 and 12.00 example matter.?? please let me know.. thanks again for all of your help

Posted by Dave Hawley on May 21, 2001 8:33 PM

Hi Darci

I think is was me that gave you the Ontime code.
I suspect that either the Workbook Open is not firing, or your code is not returning to "Macro1" after leaving to run "RefireBLP". The 5 min time should be fine, unless your code in ""RefireBLP" takes longer than 5 mins to run.

We can narrow down the problem by leaving a trail so to speak. Insert a new worksheet and call it "Test" now put this code in your macros.

Private Sub Workbook_Open()
Sheets("Test").Range("A1")="Workbook open OK!"
Run "StartTimer"
End Sub

Sub StartTimer()
Sheets("Test").Range("A2") = Time
Application.OnTime TimeValue("10:00:00"), "Macro1"
End Sub

Sub Macro1()
'
' Macro1 Macro
' 10am price cut&paste values
Sheets("Test").Range("A1") = "Macro1 OK!"
Application.Run "RefireBLP"
Sheets("Test").Range("A1") = "Returned from RefireBLP OK!"
Range("U4:U17") = Range("T4:T17").Value
Range("U4:U17").ClearContents
ActiveWorkbook.Save
Range("X8").Select
Run "StartTimer"
End Sub

...I have also modified your macro slightly.

Dave

OzGrid Business Applications