Copying cell contents a specified number of times 4 mail merge?
 

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.

Copying cell contents a specified number of times 4 mail merge?
 

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" wrote in message
...
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.

Copying cell contents a specified number of times 4 mail merge
 

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" wrote in message
...
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.