Creating new workbook from different workbook

[WLMPilot]

I am trying to figure out a way to create a work schedule for employees via
EXCEL.
I want the scheduler to use one workbook, defining certain info like
beginning date of schedule and actually creating the schedule (indicating who
is working when), then create a new workbook with the schedule in it.

1) Can this be done?

2) If it can be done, can you provide sample coding that transfers the
schedule from the original workbook to the new workbook that is sent to
employees?

Thanks,
Les

 

[J Sedoff comRemove>]

You would just need to set up a standard workbook with the date somewhere in
the worksheet. You could have in cell B1 the starting date for the week (say
8/11/08), then have a table below that automatically updates with the dates
for the rest of the week..
B4 C4 D4
=B1 =B4+1 =C4+1
(in C4 type =B4+1, then just autofill to the right to complete the rest of
the week). Each row in Column A could contain which employee is working when
(just have each employee listed in his/her own separate row, and include an
"x" if they need to work that day").

For example:
A4 B4 C4 D4 E4...
Employee 8/11 8/12 8/13 ...
John x x ...
Susan x x ...
Joe x x ...
....
....

There are several ways accomplish #2. You could just print off this
schedule and hand it out to employees (or post it on a bulletin board), have
separate worksheets for each employee in the workbook that collects his/her
data from the scheduling worksheet which could then be individually printed
off, or you could write a macro that creates a printable schedule for each
employee (maybe just print a schedule if they are scheduled to work for that
week).

Hope this helps, Jim

 

[Bob Bridges]

Creating a new workbook is easy: Just use

Set wbo = Application.Workbooks.Add

After that wbo points to the new workbook, and you can save it to a new file
whenever you're ready. Transferring the data from some workbook to a sheet
in the new one ought to be almost as easy, though I've never tried it before.
How about this?

Workbooks("Scheduler").Sheets("Schedule").Copy Before:=wbo.Sheets(1)

 

[WLMPilot]

So, after new workbook is created, the macro in the scheduler workbook would
place the data in the new workbook?

Les

 

[WLMPilot]

I don't think you understood the question or I am not understanding your
answer. See Bob Bridges post.

 

[Bob Bridges]

Yes, notice how it works:

Set wso = Application.Workbooks.Add
Workbooks("Scheduler").Sheets("Schedule").Copy Before:=wso.Sheets(1)

The first line creates the new worksheet and creates an object reference
"wso" that points to it. Then the Copy method places it before
wso.Sheets("whichever sheet you specify").

 

[WLMPilot]

Thanks for your help. I will give it a shot and see what happens.

Les