Macro for a single line mail merge?

[Ken]

Hi,

I have put together a leave tracking spreadsheet, which incorporates
statistical data as requested by my workplace. If a staff member goes on
leave for more than 5 workdays, they would like a form letter filled out
from the input data.

I know that I can use excel for mail merge to word, but I don't know how to
specify a single row of data to use, instead of all rows. I thought perhaps
I could create another worksheet and use something like (in English)
goto data sheet
Goto column header 'name'
xldown (or end I forget which)
copy cell
goto letter sheet (name cell)
paste

similarly for dates etc.

Print
Delete data from letter sheet - and run it all from a button.

This does not seem to be a very economical way to do things!
What I would like to do is for the macro to see which row it is on in the
data sheet, and use the data in a mail merge to a word document. My VB is
all but useless but I can handle simple macros, and my knowledge of formulas
is pretty good, and improving all the time. My work skills are limited. We
use Excel & Word 97 on a Win98 platform.

Any help would be greatly appreciated. Thanks,

Ken

 

[David McRitchie]

Hi Ken,
If this is something you are already setup for you can use filtering
and select their name or whatever makes that row unique.
The Mail Merge will only see the cells that filter allows it to see.

Ctrl+A (select all), Data, Filter, Autofilter
choose name out of the appropriate drop-down box at the top.
If you apply additional filters you add further restrictions.

When finished to remove filter you can use
Data, Filter, Autofilter which will uncheck the Autofilter

You can identify which filtering is in place by the row numbers and
column letters turning Blue instead of black..
--

 

[Ken]

Thanks David,

I was under the impression (wrongly) that mail merge used all rows even in
filtered. I'll have to experiment for a simple way for people to do this.
Unfortunately I won't be the end-user, otherwise I don't think I'd bother
going to this trouble.

Thanks again,

Ken

Hi Ken,
If this is something you are already setup for you can use filtering
and select their name or whatever makes that row unique.
The Mail Merge will only see the cells that filter allows it to see.

Ctrl+A (select all), Data, Filter, Autofilter
choose name out of the appropriate drop-down box at the top.
If you apply additional filters you add further restrictions.

When finished to remove filter you can use
Data, Filter, Autofilter which will uncheck the Autofilter

You can identify which filtering is in place by the row numbers and
column letters turning Blue instead of black..
--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hi,

I have put together a leave tracking spreadsheet, which incorporates
statistical data as requested by my workplace. If a staff member goes on
leave for more than 5 workdays, they would like a form letter filled out
from the input data.

I know that I can use excel for mail merge to word, but I don't know how to
specify a single row of data to use, instead of all rows. I thought perhaps
I could create another worksheet and use something like (in English)
goto data sheet
Goto column header 'name'
xldown (or end I forget which)
copy cell
goto letter sheet (name cell)
paste

similarly for dates etc.

Print
Delete data from letter sheet - and run it all from a button.

This does not seem to be a very economical way to do things!
What I would like to do is for the macro to see which row it is on in the
data sheet, and use the data in a mail merge to a word document. My VB is
all but useless but I can handle simple macros, and my knowledge of formulas
is pretty good, and improving all the time. My work skills are limited. We
use Excel & Word 97 on a Win98 platform.

Any help would be greatly appreciated. Thanks,

Ken