timed macro
 

My customer has a WS, with a pivot table. In the WS he has a button
with a macro which sorts the pivot data into suppliers, and make a view
screen specific to each supplier. The problem is he has to remember to
push the button !!! Does anyone know if it is posible to run this
macro automatically, so that it exicutes every morning @ 8:00 am. Can
you add code to the macro, or does it have to be something completely
different.
Thanks

timed macro
 

On 3 Mar 2006 03:16:53 -0800, "Decreenisi"
wrote:

My customer has a WS, with a pivot table. In the WS he has a button
with a macro which sorts the pivot data into suppliers, and make a view
screen specific to each supplier. The problem is he has to remember to
push the button !!! Does anyone know if it is posible to run this
macro automatically, so that it exicutes every morning @ 8:00 am. Can
you add code to the macro, or does it have to be something completely
different.
Thanks

Is the time of 8.00 am important? By that I mean is the workbook
reliant on data that is only refreshed every day by 8.00 am at the
latest, and hence you can't push the button until post 8.00 am

If it's not important and it just needs to be run, you could attach
the macro to the workbook open event.

You can pause a macro running with the Application.Wait command, but
that pauses all other Excel Activity as well, in which case you could
use something like the following in the workbook open event

Private Sub Workbook_Open()
Dim PauseUntil as Double

PauseUntil = TimeSerial(8,0, 0)
Do While Time() < PauseUntil
Loop

.....your Pivot Table Code here

End Sub

Alternatively you could simply put

MsgBox "Push the bloody button!"

in the Workbook open event.

Probably not exactly what you're looking for, so I'll be interested to
see what others come up with.

HTH

Richard Buttrey
__

timed macro
 

hi,
yes there is.
the Application.OnTime method. But the file has to be open for it to fire on
time. here is code i use. if it is friday, the code skips the weekend. if it
is not friday, the code fires a 5am the next day. if he does not have the
file open before 8am then the code will not fire untill the next 8 oclock.
look up Application.OnTime in VB help for more info. "yourMacro" is the name
of the macro you want to run at the specified time.

Sub macALaunchMR()

If Weekday(Now()) = 6 Then '1 = Sunday, 2 = Monday, 3 = Tuesday, ect

Application.OnTime Now() + 2.5 + TimeValue("00:00:03"), "yourMacro"
Else
Application.OnTime TimeValue("05:00:00"), "yourMacro"
End If

End Sub
suggestion. if he is that forgetfull then why not put the code in a file
open event so that the code runs when he opens the file?

regards
FSt1

"Decreenisi" wrote:

My customer has a WS, with a pivot table. In the WS he has a button
with a macro which sorts the pivot data into suppliers, and make a view
screen specific to each supplier. The problem is he has to remember to
push the button !!! Does anyone know if it is posible to run this
macro automatically, so that it exicutes every morning @ 8:00 am. Can
you add code to the macro, or does it have to be something completely
different.
Thanks

timed macro
 

"FSt1" wrote in message
...
hi,
yes there is.
the Application.OnTime method. But the file has to be open for it to fire
on
time.

No it doesn't, it will open if closed and fire.