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

K

KST8WCT

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.
 
R

Rick Rothstein

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
 
K

KST8WCT

Thank you, Rick. This worked perfectly!

Rick Rothstein said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top