Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2013
    Thanked 0 Times in 0 Posts

    How to build a macro for autonumbering labels


    I am wondering how I can build a macro for increasing the number on a set of labels we have to make for our cases we ship out.

    Typically, each label has the same set of information. They are set up 4 to a page. One label will look like this:

    Vendor #: 12345
    PO# 76377773
    Weight: 3.0 lbs
    Description: Product 1
    Case Number 1 of 245

    So, we have 4 to a page, right? What we end up doing now is change all 4 labels on the page to read 1, then 2, then 3 then 4. We print it out.
    After the label is printed out, we go back to the template and change 1, 2, 3 and 4 to read 5, 6, 7 and 8.

    Is there any way to automate this so we don't have to continually go back and change the numbers by hand?

    Thanks in advance.

  2. #2
    3 Star Lounger
    Join Date
    Apr 2012
    Milton, Ontario
    Thanked 25 Times in 25 Posts
    Can you do it by a mail merge?
    1. Create an Excel document with the headers required; Vendor, PO#, Weight, Description, Number, Of_Labels.
    2. Directly below, enter the required information once; 12345, 76377773, 3.0 lbs, Product 1, 1, of 245
    3. Copy the information, not including the header row, straight down to line 246 (245 lines plus one for the header)
    4. Go to the Number column and highlight the first 1, hover over the bottom right corner of the cell until the cursor changes to a + sign, then drag it down while holding the Ctrl Key. This will cause the numbers to increment by 1 for each cell you go down.
    5. Save the list where you can easily find it.

    Next, create a mail merge document in Word.
    1. In the mail merge area, select the Label you're using.
    2. Insert the merge fields that you created using the Word merge tools to select and insert them. (I can't be very specific as I don't know which version of Word you're using)
    3. Insert the "Next Record" field at the bottom of each individual label to force the "Number" field to increment.

    Then, either print to a new document, which will show you exactly what you're getting ahead of time, and print the new document - or (once you're more familiar with it) print directly to the printer if you're confident all's set up correctly.

    Note: Your Excel header information cannot have any words with spaces. If you want to have two or more words, join with dashes or underscores.

    I don't know what level you're at, but at first a mail merge may seem difficult, but once you've done it a few times it will be real easy. You can save the Excel and Word templates and set one up in a couple of minutes.

    Let me know if you need more guidance.

    Doug Mac

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2006
    Maryland, USA
    Thanked 66 Times in 56 Posts
    For several options, see Sequentially Numbered Labels.

    Pam Caswell

Posting Permissions

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