Mail Merge with Variables (XP 2002)

Topic

New Reply

I need to print the labels from a spread sheet. I understand I can use the mail merge tool of Word to achieve this job. However, the label print is a little complicated than normal.

In the spread sheet, column A is the Clien_Name. Column B is the Num_Labels, the number of label to be printed for this particular client. The values of Num_Lables for each client are different.

How to solve this problem?

Thanks,

Reply | Quote

Replies

You could write a macro that generates a sheet with as many copies of a row as the number in column B indicates, and use this sheet as data source for the mail merge.

Reply | Quote

Hi Hans,

Thank you for your prompt response on it.

This VBA looks complicated to me. Can you write me a sample macro that will create a list on a new sheet, not on the original sheet?

Reply | Quote

Try this, I have inserted comments in the code to explain what it does:

Sub GenerateSheet()
Dim wshS As Worksheet
Dim wshT As Worksheet
Dim r As Long
Dim m As Long
Dim i As Long
Dim t As Long

‘ Source sheet
Set wshS = Worksheets(“Original”)
‘ Create new sheet as target
Set wshT = Worksheets.Add(After:=Worksheets(Worksheets.Count))

‘ Copy first row
wshS.Rows(1).Copy Destination:=wshT.Rows(1)

‘ Initialize values
t = 1
m = wshS.Range(“A” & wshS.Rows.Count).End(xlUp).Row

‘ Loop through rows of source sheet
For r = 2 To m
‘ Copy row as many times as indicated by column B
For i = 1 To wshS.Range(“B” & r)
t = t + 1
wshS.Rows®.Copy Destination:=wshT.Rows(t)
Next i
Next r

‘ Autofit columns on target sheet
wshT.Columns.AutoFit
End Sub

The attached sample workbook has a command button that will run this macro.

Reply | Quote

Hans,

Right on the target! Thanks a lot.

Reply | Quote