"Mail merge" in Excell?

[User]

Is it possible to do a "mail merge" type of process with Excel?

A co-worker of mine has a data collection form entered into an Excel 97
spreadsheet. We would like to print out about 900 copies of this form,
each with a different name, employee number etc at the top.
The data is saved in an Excel file.

I couldn't seem to locate any built-in features of Excel to solve this
problem. I would guess a visual basic macro would solve this, but my macro
skills are not quite that advanced yet :)

Can anyone offer some help with this?

TIA

[Tom Ogilvy]

Assume list of names is on Sheet2 in A1:A900

Assume form is on a sheet named Form1 and the name goes in cell B9

Dim rng as Range
With Worksheets("Sheet2")
Set rng = .Range(.Cells(1,1),.Cells(1,1).End(xldown))
End with
for each cell in rng
worksheets("Form1").Range("B9").Value = cell.Value
worksheets("Form1").Printout
Next

This further assumes that the form1 page is configured to print out as you
want it.

Regards,
Tom Ogilvy

User <us...@microsoft.com> wrote in message
news:Oo0kVh4TBHA.1448@tkmsftngp07...

[User]

Thanks so much!

"Tom Ogilvy" <twog...@email.msn.com> wrote in message
news:ueEMdn4TBHA.1848@tkmsftngp03...

[User]

Oakie, doakie, all is fine but I need to use Name, EmployeeNo and Code from
Sheet2. Im sure it's so simple to implement but I just dont know how :)

Help again pls!

TIA

[David McRitchie]

Rather than using a microsoft domain could you please use your real
name and email address or something that could not be a domain at all.
Just for the heck of it I like to see when someone from microsoft
actually replies in the newsgroup (perhaps once a week).

In Outlook Express this is set up in Tools, Accounts, Properities

FYI, Excel can act as the database for MS Word Mail Merge, see
http://www.mvps.org/dmcritchie/excel/mailmerg.htm
if you ever need to it that way. But it sounds like Tom's answer is
exactly what you wanted. I realize this was for someone else, but
the best kind of thanks is when thanks starts out with "it works" which also
means it was actually used.

HTH,
David McRitchie, Microsoft MVP - Excel [alternate/main sites below]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm

"User" <us...@microsoft.com> wrote in message news:uUP9Uq4TBHA.2076@tkmsftngp03...

[Tom Ogilvy]

Dim rng as Range
With Worksheets("Sheet2")
Set rng = .Range(.Cells(1,1),.Cells(1,1).End(xldown))
End with
for each cell in rng
worksheets("Form1").Range("B9").Value = cell.Value

Worksheets("Form1").Range("D9").Value = cell.offset(0,1).Value
Worksheets("Form1").Range("C10").Value = cell.Offset(0,2).Value
worksheets("Form1").Printout
Next

Just assign each source cell to the appropriate cell in the form.

Regards,
Tom Ogilvy

User <us...@microsoft.com> wrote in message

news:useXUg5TBHA.912@tkmsftngp07...

[Dave Peterson]

PMFJI, but how about this:

Sub testme()

Dim rng As Range

With Worksheets("Sheet2")
Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
End With

For Each cell In rng
With Worksheets("Form1")
'name
.Range("B9").Value = cell.Value
'employee
.Range("B11").Value = cell.Offset(0, 5).Value
'code
.Range("B22").Value = cell.Offset(0, 7).Value
.PrintOut
End With
Next

End Sub

Adjust B9, B11, B22 of the Form1 worksheet accordingly.

Also adjust the offset(0,X). This means use the same row that contains the name
but go X cells to the right.

cell.offset(0,5) <-- 5 to right
cell.offset(0,7) <-- 7 to right

--

Dave Peterson
ec3...@msn.com

[User]

Duh!

That was easy, thanks so much again, I should manage to make it work from
here :)