Automate simple process to run at 8 am daily

[Max]

I've got an excel file which will remain open in a PC which is left on 24x7.
At say, 8:00 am daily, I need a sub to copy a sheet: Live (codename), then
insert a new sheet, do a paste special as values & formats, then rename the
new sheet as the date in ddmmm format, eg: 31Dec, and move this to be the
leftmost sheet. Then to save the file. Thanks

 

[Alan]

This is usually accomplished using an operating system utility. If
you are using Windows, you can do this via the Windows Task Scheduler
(see http://www.iopus.com/guides/winscheduler.htm).

I am not a Linux user, but I believe that you use the "cron" utility
(see http://www.linuxhelp.net/guides/cron/) to do this on a system
running Linux.

Alan

 

[Max]

Thanks, but I have to do it in/via Excel. Any thoughts on the sub, pl?

 

[Alan]

I do not understand the problem with involving the operating system.
The idea is that the task scheduler simply opens the workbook at the
appropriate time. The workbook contains the VBA code you want to run,
and you can use the Workbook_Open() event (see http://www.ozgrid.com/VBA/auto-run-macros.htm)
to make the code run automatically. If you like

If, for some reason, you cannot do this, the Excel workbook containing
the VBA code msut be open when it needs to run. Are you assuming
that?

If so, you can use the Now() function to get the current time and
loop until you reach or pass the trigger time. If you have not
passed the trigger time, you can use Sleep() (see
http://www.your-save-time-and-improve-quality-technologies-online-resource.com/vba-sleep-excel.html)
to wait for a while to check again.

If other VBA code will be running, you may need to insert "DoEvents"
in the loop.

Alan

 

[ron]

I've got an excel file which will remain open in a PC which is left on 24x7.
At say, 8:00 am daily, I need a sub to copy a sheet: Live (codename), then
insert a new sheet, do a paste special as values & formats, then rename the
new sheet as the date in ddmmm format, eg: 31Dec, and move this to be the
leftmost sheet. Then to save the file. Thanks

Max...Take a look at the "On Time" method in VBA help...Ron

 

[Max]

I do not understand the problem with involving the operating system.
The problem is I don't have admin rights, and I don't want to delve into
that area

If, for some reason, you cannot do this, the Excel workbook containing
the VBA code msut be open when it needs to run. Are you assuming
that?

Yes, think I did say that in my posting's opening line:

 

[Alan]

Using "OnTime", as Max suggested, is a better option.

Alan