MrExcel Publishing
Your One Stop for Excel Tips & Solutions

timed macro


Posted by Darci on May 18, 2001 11:02 AM

I was wondering if it is possible to run a macro at a specified time. i am not familiar with vba, but i have created a macro through the icon and letting it record what i do. i need it to cut and paste values from one excel sheet to a blank one at 12:00 each day, is this possible.???
thanks for any advice...
Darci


Posted by Dave Hawley on May 18, 2001 11:56 AM

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

Posted by Darci on May 18, 2001 12:16 PM

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:


Posted by Dave Hawley on May 18, 2001 12:32 PM

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

Posted by Always looking on May 18, 2001 1:18 PM

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

Posted by Dave Hawley on May 18, 2001 2:17 PM

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

Posted by Always looking on May 19, 2001 10:22 AM

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


Posted by Darci on May 21, 2001 8:51 AM

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

Posted by Darci on May 21, 2001 8:55 AM

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