timed macro

Hi Darci

You can use the Application.Ontime Event to do this. You will need to place it in the Workbook_Open event.

To do this right click on the sheet picture, top left next to "File" and select "View Code". Paste in this code:

Private Sub Workbook_Open()
Application.OnTime TimeValue("12:00:00"), "MyMacro"
End Sub

The text "MyMacro" will need to be changed to the name of your macro. This method requires that the file is opened each day to fire the Workbook_Open event. If this is an issue let me know and I'll show you a way around this.

Dave

OzGrid Business Applications

Open file problem

The file is constantly open all day and night long because it has links that are constantly updated, is there a way around this???
Thanks again

Paste in this code:

Re: Open file problem

In that case place this code in the Workbook_Open Event.

Private Sub Workbook_Open()
Run "StartTimer"
End Sub

The in any normal module place this:

Sub StartTimer()
Application.OnTime TimeValue("12:00:00"), "MyMacro"
End Sub

Sub MyMacro()
'Your code
Application.OnTime TimeValue("11:55:00"), "StartTimer"
End Sub

Again the "MyMacro" is your macro that you want to run at 12:00

Dave
OzGrid Business Applications

David : If you have a minute

David :

I'm always looking for tips & since your always
coming up with some of the best solutions, I try
to always check your replies so I was looking @ the
code. I understand everthing except and "please
excuse my ignorance" but not sure why the
line of code at the bottom of "Mymacro" is needed:

Application.OnTime TimeValue("11:55:00"), "StartTimer"

If you have a minute to spare - not sure how this
triggers StartTimer, since it's buried @ bottom of
code.

thxs

In that case place this code in the Workbook_Open Event. Run "StartTimer" End Sub The in any normal module place this: Sub StartTimer() End Sub Sub MyMacro() 'Your code Application.OnTime TimeValue("11:55:00"), "StartTimer" End Sub Again the "MyMacro" is your macro that you want to run at 12:00 Dave

Re: David : If you have a minute

Hello, Always looking :o)

The line :
Application.OnTime TimeValue("11:55:00"), "StartTimer"

is used to run the macro "StartTimer", which in turn will run the "MyMacro" code. It is basically an endless loop.

To see what i mean try this:

Sub StartTimer()
Application.OnTime Now + TimeValue("00:00:10"), "MyMacro"
End Sub

Sub MyMacro()
Range("A1") = Range("A1") + 1
Application.OnTime Now + TimeValue("00:00:01"), "StartTimer"
End Sub

No run "StartTimer" and watch cell A1.

Dave

OzGrid Business Applications

Makes perfect sense - Guess that's why you get the "Big" bucks

I was losing it on the "Private Sub
Workbook_Open()" code. Only needed to make sure
"StartTimer" in the event the file was ever closed.

Like I said "Please excuse my ignorance"
Thxs again.

Hello, Always looking :o) The line : Application.OnTime TimeValue("11:55:00"), "StartTimer" is used to run the macro "StartTimer", which in turn will run the "MyMacro" code. It is basically an endless loop. To see what i mean try this: Sub StartTimer() Application.OnTime Now + TimeValue("00:00:10"), "MyMacro" End Sub Sub MyMacro() Range("A1") = Range("A1") + 1 Application.OnTime Now + TimeValue("00:00:01"), "StartTimer" End Sub No run "StartTimer" and watch cell A1. Dave

Troubles

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 workbook open event)
Private Sub Workbook_Open()
Run "StartTimer"
End Sub
Sub StartTimer()
Application.OnTime TimeValue("10:00:00"), "Macro1"
End Sub
(in the general module)
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"
End Sub

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

typo

(in the workbook open event) Private Sub Workbook_Open() Run "StartTimer" End Sub
(in the general module)
Sub StartTimer() Application.OnTime TimeValue("10:00:00"), "Macro1" End Sub 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" End Sub 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

I misplaced the general heading. see revised copy