Merge question
 

First off, Excel is not my strong point. I know the basics and that's about it.

I'm working as a temp for a local healthcare business office. They produce
reports for each employee (~60) quarterly that tells them how much PTO and
STD times they have remaining for the year.

The PTO is a set amount per year. As it's used, Darla edits a table in Word.
The STD accrues at 2.77 hrs per week. Darla has an Excel spreadsheet set up
to add add that amount weekly and then she edits it if time has been used.

When she is ready to send out a new report, she makes 60 copies of a blank
form. The employee's name. location, PTO, and STD is filled in by hand. When
they are all done, she needs to make a copy of each for her log.

There HAS to be a better way! Surely there is a way to do this as a mail
merge. If so, is there a way to specify that the STD time is the amount in
Row whatever and the Last column that has an entry? I can easily convert the
Word table to a spreadsheet because it doesn't accrue.

I'm looking for suggestions. Preferably worded for someone who needs those
bright yellow books. ;-)


--
JoAnn Paules
MVP Publisher

Jo: Is there some reason you can't just use a mail merge? Are there multiple
rows per employee or what? I can probably help one way or another,
especially if I have a sample file. Feel free to email me directly.
Otherwise, I really need to know if there's one row per employee or what...
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"JoAnn Paules - MVP Publisher"
wrote in message
...
First off, Excel is not my strong point. I know the basics and that's
about it.

I'm working as a temp for a local healthcare business office. They produce
reports for each employee (~60) quarterly that tells them how much PTO and
STD times they have remaining for the year.

The PTO is a set amount per year. As it's used, Darla edits a table in
Word.
The STD accrues at 2.77 hrs per week. Darla has an Excel spreadsheet set
up
to add add that amount weekly and then she edits it if time has been used.

When she is ready to send out a new report, she makes 60 copies of a blank
form. The employee's name. location, PTO, and STD is filled in by hand.
When
they are all done, she needs to make a copy of each for her log.

There HAS to be a better way! Surely there is a way to do this as a mail
merge. If so, is there a way to specify that the STD time is the amount in
Row whatever and the Last column that has an entry? I can easily convert
the
Word table to a spreadsheet because it doesn't accrue.

I'm looking for suggestions. Preferably worded for someone who needs those
bright yellow books. ;-)


--
JoAnn Paules
MVP Publisher

Joanna,

It sounds to me that you could easily setup a template form, then have a VBA
macro that loops through the 60 employees and fills in the variable data and
prints it.

The code would be something like

For i = 2 To Cells(Row.Count,"A").End(xlUp)Row
Worksheets("Template").Range("A1").Value = Cells(i,"A").Value
Worksheets("Template").Range("H5").Value = Cells(i,"B").Value
Worksheets("Template").Range("M5").Value = Cells(i,"C").Value
Worksheets("Template").Print
Next i

obviously the code would be adjusted to suit the data.

If you want to send me a workbook, suitably adjusted to protect the data,
and an electronic sample form, IU could quickly do that for you.

--
HTH

Bob Phillips

"JoAnn Paules - MVP Publisher"
wrote in message
...
First off, Excel is not my strong point. I know the basics and that's
about it.

I'm working as a temp for a local healthcare business office. They produce
reports for each employee (~60) quarterly that tells them how much PTO and
STD times they have remaining for the year.

The PTO is a set amount per year. As it's used, Darla edits a table in
Word.
The STD accrues at 2.77 hrs per week. Darla has an Excel spreadsheet set
up
to add add that amount weekly and then she edits it if time has been used.

When she is ready to send out a new report, she makes 60 copies of a blank
form. The employee's name. location, PTO, and STD is filled in by hand.
When
they are all done, she needs to make a copy of each for her log.

There HAS to be a better way! Surely there is a way to do this as a mail
merge. If so, is there a way to specify that the STD time is the amount in
Row whatever and the Last column that has an entry? I can easily convert
the
Word table to a spreadsheet because it doesn't accrue.

I'm looking for suggestions. Preferably worded for someone who needs those
bright yellow books. ;-)


--
JoAnn Paules
MVP Publisher