Ordering data for Mail Merge, printing to labels

[languid_heap]

Hi all,

I've created a spreadsheet containing the following fields from an
order form:

Record Number | Customer | Pickup Time | Item 1 | Item 2 | Item 3 |
Item 4 | Item 5 | Total Items

- Record Number is a sequential number beginning at 1 and ascending in
order. The Record Number is assigned when the order is entered into the
worksheet, and is used as a unique identifier.
- Customer is the name of the customer.
- Pickup Time is the time the order will be picked up.
- Item 1 through Item 5 are the products the customer has ordered.
These fields may have any value from 0 to say, 100.
- Total Items is "=SUM(E2:I2)"

I need to create labels, preferably using mail merge in Word. Each
label needs to have the name of the customer, the pickup time, Item
name, and package number of package total (For example, if there are 4
total items in the order, the first label would list 1/4, the second
label would list 2/4, the third label 3/4, and fourth label 4/4).

Sample Data:


Code:
--------------------

Record Number | Customer | Pickup Time | Item 1 | Item 2 | Item 3 | Item 4 | Item 5 | Total Items |
1 | Smith, Jo | 9:30am | 1 | 0 | 3 | 0 | 0 | 4 |
--------------------


For this record, I'd need 4 labels printed. Each of the 4 labels would
have the same record number, customer name, and pickup time. In
addition each label also needs the Item Name (Item 1, Item 3) and the
Item number out of Item Total (1/4, 2/4, 3/4, 4/4).

So far, I can easily get the Customer, Pickup Time, Record Number and
Total Items. What I'm having trouble with is
1) When a record has more than one item, I need mail merge to print the
number of labels equal to the Total Item column. If Jo Smith has 4
items ordered, I need 4 labels.
2) I need a way for the mail merge to print the item number of item
total (1/4, 2/4, 3/4, 4/4) on the appropriate label.
3) I need a way to get the Item Name on the appropriate label.

I'm sure I'm overlooking some simple solutions here -- any thoughts?

Thanks in advance

 

[David McRitchie]

Printing Multiple Labels based on Column A (#multilabel)
http://www.mvps.org/dmcritchie/excel/mailmerg.htm#multilabel

change from using Column a to Column i
Cells(ir, 9)
to
Cells(ir, 9)

The macro will create a new worksheet

 

[languid_heap]

Thanks very much for your help -- with a little reworking I was able t
do exactly what I needed.

Best, L