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

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
--
I appreciate any feedback. Please don''t be scared to say that "Yes,"
I/someone else did answer your question. Thank you.

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" wrote:
> 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?

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

Les


"Bob Bridges" wrote:

> 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" wrote:
> > 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?

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

"J Sedoff" wrote:

> 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
> --
> I appreciate any feedback. Please don''t be scared to say that "Yes,"
> I/someone else did answer your question. Thank you.

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" wrote:
> So, after new workbook is created, the macro in the scheduler workbook would
> place the data in the new workbook?
>
> --- "Bob Bridges" wrote:
> > 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" wrote:
> > > 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 for your help. I will give it a shot and see what happens.

Les

"Bob Bridges" wrote:

> 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" wrote:
> > So, after new workbook is created, the macro in the scheduler workbook would
> > place the data in the new workbook?
> >
> > --- "Bob Bridges" wrote:
> > > 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" wrote:
> > > > 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?