I have a list of part numbers and quantities that need to be printed on
labels each day. The list will have between 20 and 250 part numbers and
different quantities each day.

part qty
1x 5
2t 23
3b 7

How do I tell Excel to take the part number and paste it the number of times
associated with the quantity for that record into another worksheet and then
repeat until all the records have been processed? This worksheet with the
list will be the data source for a mail merge to print the labels.

Does this code do what you want (change the appropriate assignments to match
your actual layout)...

Sub MakeMailMerge()
Dim X As Long, Z As Long, Qty As Long, Rw As Long
Dim StartRow As Long, LastRow As Long
Dim Source As String, Destination As String
StartRow = 2
Source = "Sheet3"
Destination = "Sheet5"
With Worksheets(Source)
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = StartRow To LastRow
Qty = .Cells(X, "B").Value
For Z = 1 To Qty
Rw = Rw + 1
Worksheets(Destination).Cells(Rw, "A").Value = .Cells(X, "A").Value
Next
Next
End With
End Sub

--
Rick (MVP - Excel)


"KST8WCT" <(E-Mail Removed)> wrote in message
news:6A3B7549-B1C1-43D5-9E0A-(E-Mail Removed)...
>I have a list of part numbers and quantities that need to be printed on
> labels each day. The list will have between 20 and 250 part numbers and
> different quantities each day.
>
> part qty
> 1x 5
> 2t 23
> 3b 7
>
> How do I tell Excel to take the part number and paste it the number of
> times
> associated with the quantity for that record into another worksheet and
> then
> repeat until all the records have been processed? This worksheet with the
> list will be the data source for a mail merge to print the labels.
>
>

Thank you, Rick. This worked perfectly!

"Rick Rothstein" wrote:

> Does this code do what you want (change the appropriate assignments to match
> your actual layout)...
>
> Sub MakeMailMerge()
> Dim X As Long, Z As Long, Qty As Long, Rw As Long
> Dim StartRow As Long, LastRow As Long
> Dim Source As String, Destination As String
> StartRow = 2
> Source = "Sheet3"
> Destination = "Sheet5"
> With Worksheets(Source)
> LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
> For X = StartRow To LastRow
> Qty = .Cells(X, "B").Value
> For Z = 1 To Qty
> Rw = Rw + 1
> Worksheets(Destination).Cells(Rw, "A").Value = .Cells(X, "A").Value
> Next
> Next
> End With
> End Sub
>
> --
> Rick (MVP - Excel)
>
>
> "KST8WCT" <(E-Mail Removed)> wrote in message
> news:6A3B7549-B1C1-43D5-9E0A-(E-Mail Removed)...
> >I have a list of part numbers and quantities that need to be printed on
> > labels each day. The list will have between 20 and 250 part numbers and
> > different quantities each day.
> >
> > part qty
> > 1x 5
> > 2t 23
> > 3b 7
> >
> > How do I tell Excel to take the part number and paste it the number of
> > times
> > associated with the quantity for that record into another worksheet and
> > then
> > repeat until all the records have been processed? This worksheet with the
> > list will be the data source for a mail merge to print the labels.
> >
> >
>
>