Create macro to copy data on a scheduled basis
 

I am running a spreadsheet (Excel 2003) with dynamic data updates that I want
to capture production data toward the end of a shift before it resets for the
next shift. The objective is to capture data from a source file and save it
somewhere else for historical purposes. Below was my idea on how this might
be done; however, if you offer a better solution, please let me know.

My thought was to schedule a task linked to Excel to launch in the last
minute of the shift every day. As part of the process of executing the task
tied to opening the file (using a VB module tied to "ThisWorkbook" in a file
called Prod Data.xls), I want to execute a macro to go to the source file
(called "Scoreboard.xls" and worsheet called "Prod-Eff") and copy all the
data on that worksheet into "Prod Data.xls".

After copying and pasting into "Prod Data.xls", the macro would execute a
"Save As..." command to save it into another directory for historical
purposes. Essentially, the idea is to use the scheduled task Excel file (Prod
Data.xls) as a conduit to retrieve the source data (without affecting that
worksheet from continuing its updating), save and store the data into another
folder. Finally, I want the "Prod Data.xls" conduit file to close after
completing the task - until the next day when the scheduled task executes
again.

Can someone help me with the Module1 code that I must write that will be tied
to the Scheduled Task "Prod Data.xls" file? Again, I know it must be tied to
VBAProject (Prod Data.xls) and attached to "ThisWorkbook" to ensure the macro
runs upon opening.

Sorry for being so wordy - but I wanted to try to clearly explain my idea.

I advance, thanks for your support.