Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Texas, USA
    Posts
    374
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Mail Merge with Variables (XP 2002)

    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,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail Merge with Variables (XP 2002)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Texas, USA
    Posts
    374
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Mail Merge with Variables (XP 2002)

    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?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail Merge with Variables (XP 2002)

    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.
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Texas, USA
    Posts
    374
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Mail Merge with Variables (XP 2002)

    Hans,

    Right on the target! Thanks a lot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •