Move data twice a day

[TCL28]

Hello all,
I am trying to write a macro that will automatically move the date in row one down one row and insert the data in a different sheet into row one. The catch is I want the program to do this automatically for me each day at 5pm and 5am. Writting the macro to mvoe the data and insert and row is not hard but to make it do it automatically twice a day at 5 is a little above my level. Any help would be greatly appreciated.

Regards,

Tom

 

[Smitty]

Have a look at Chip Pearson's site: Scheduling Procedures With OnTime

Hope that helps,

Smitty

 

[Oaktree]

Assuming you can write the macro, one method would be:

1) create a "loader file" whose sole purpose is to, via workbook_open event, open the other file, run the other file's macro, close the other file, and close itself. Such as:

Private Sub Workbook_Open()
Application.Workbooks.Open ("\\path\Other Workbook.xls")
Application.Run "'Other Workbook.xls'!macro"
Workbooks("Other Workbook").Save
Application.Quit
End Sub

2) Use a digital signature to sign the macro in the loader file (so you won't be prompted with the enable macros button, assuming your security is on medium)

3) Use windows scheduler to open your loader file at the appropriate times. Since the macro is triggered in the workbook_open event, opening the file via scheduler is enough to trigger the process.

 

[TCL28]

Oaktree,

I want to do what you wrote but it is way above me. I don't know the first thing about using the scheduler and I did not know that I could have the macro automatically run once the workbook is opened. Can you dumb it down for me?

Tom

 

[Oaktree]

Scheduler is part of Windows. I'm not sure if it varies by version of Windows, but on my PC, I get there from start --> all programs --> accessories --> system tools --> scheduled tasks. Just add a scheduled task referencing the loader file and specify the timing of the task.

To automatically have a macro run when a workbook is open, use a Workbook_Open event (see Excel VBA help on workbookopen event). In the ThisWorkbook module, you'd put:

Private Sub Workbook_Open()
'In here, put the code you want to execute when the workbook opens
End Sub

See Excel help for "Create your own digital signature" for step 2 in my previous post.