locked
How can I do a mail merge to make labels of stock with various levels? vba? RRS feed

  • Question

  • HI all, I asked a simlar question elsewhere and was told to ask here so hopefully someone can help.

    What im trying to do is do a mailmerge from a list in excel to print out labels for our shop. The problem is the stock has various levels eg.

    plants  87

    herbs 28

    bag of carrots 238

    etc etc..

     so on excel I have to columns 'Name' and 'Quantity' and want labels fort each item in the quantities in the quantity column so 87 'plant' labels etc

    Is this possible? I have over 1000 items of stock and cutting and pasting is not an option!  I have never used mail merge before and my pc knowlege is basic. I never even hered of VBA!

     

    I would really appriciate some help

     

    Friday, July 23, 2010 9:01 AM

Answers

  • If you have the names and the quantities in sheet 1 of the workbook and you then run a macro containing the following code;

    Dim i As Long, j As Long, k As Long
    Dim arrData As Variant
    With ActiveWorkbook
        arrData = .Sheets(1).Range("A1").CurrentRegion.Value
        With .Sheets(2).Range("A1")
            .Offset(0, 0) = "Name"
            k = 1
            For i = 2 To UBound(arrData, 1)
                For j = 1 To arrData(i, 2)
                    .Offset(k, 0) = arrData(i, 1)
                    k = k + 1
                Next j
            Next i
        End With
    End With

    it will populate Sheet 2 with

    Name
    Plants
    Plants for 87 rows in total, then
    Herbs
    Herbs for 28 rows in total, then
    Bags of Carrots
    Bags of Carrots for 238 rows in total

    Then you can use Sheet 2 as the mail merge data source.

    -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "chris1234554321" wrote in message news:6dddc602-b235-49ea-aa6c-724ce5a23795@communitybridge.codeplex.com...

    HI all, I asked a simlar question elsewhere and was told to ask here so hopefully someone can help.

    What im trying to do is do a mailmerge from a list in excel to print out labels for our shop. The problem is the stock has various levels eg.

    plants  87

    herbs 28

    bag of carrots 238

    etc etc..

    so on excel I have to columns 'Name' and 'Quantity' and want labels fort each item in the quantities in the quantity column so 87 'plant' labels etc

    Is this possible? I have over 1000 items of stock and cutting and pasting is not an option!  I have never used mail merge before and my pc knowlege is basic. I never even hered of VBA!

    I would really appriciate some help


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    • Marked as answer by Fafafaalex Friday, July 30, 2010 3:01 AM
    Friday, July 23, 2010 11:17 PM