Duplicating Numbers For Mail Merge

[Dazzawm]

I have a list of numbers in column a and how many labels i need in column b. The only way I know how to do mail merge is have the amount of each label by copying/pasting. Is there an easy way to do this especially if I have several hundred rows?

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 60px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 60px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Before</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">After</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">Part</TD><TD style="TEXT-ALIGN: center">Required</TD><TD> </TD><TD style="TEXT-ALIGN: center">Part</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center">ABC1234</TD><TD style="TEXT-ALIGN: center">3</TD><TD> </TD><TD style="TEXT-ALIGN: center">ABC1234</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center">ABC4567</TD><TD style="TEXT-ALIGN: center">4</TD><TD> </TD><TD style="TEXT-ALIGN: center">ABC1234</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center">ABC789</TD><TD style="TEXT-ALIGN: center">2</TD><TD> </TD><TD style="TEXT-ALIGN: center">ABC1234</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">ABC4567</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">ABC4567</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">ABC4567</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">ABC4567</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">ABC789</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">ABC789</TD></TR></TBODY></TABLE>

 

[VoG]

Try this:

Sub atest()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    Range("D" & Rows.Count).End(xlUp).Offset(1).Resize(Range("B" & i).Value) = Range("A" & i).Value
Next i
End Sub

 

[Dazzawm]

Thanks VoG. What if I have more info in columns next to it like below?

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:60px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:60px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:center; ">BEFORE</td><td > </td><td > </td><td > </td><td style="text-align:center; ">AFTER</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; ">ABC1234</td><td style="text-align:center; ">YES</td><td style="text-align:center; ">HELLO</td><td style="text-align:center; ">3</td><td style="text-align:center; ">ABC1234</td><td style="text-align:center; ">YES</td><td style="text-align:center; ">HELLO</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; ">ABC4567</td><td style="text-align:center; ">NO</td><td style="text-align:center; ">GOODBYE</td><td style="text-align:center; ">4</td><td style="text-align:center; ">ABC1234</td><td style="text-align:center; ">YES</td><td style="text-align:center; ">HELLO</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:center; ">ABC789</td><td style="text-align:center; ">YES</td><td style="text-align:center; ">HI</td><td style="text-align:center; ">2</td><td style="text-align:center; ">ABC1234</td><td style="text-align:center; ">YES</td><td style="text-align:center; ">HELLO</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:center; ">ABC4567</td><td style="text-align:center; ">NO</td><td style="text-align:center; ">GOODBYE</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:center; ">ABC4567</td><td style="text-align:center; ">NO</td><td style="text-align:center; ">GOODBYE</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:center; ">ABC4567</td><td style="text-align:center; ">NO</td><td style="text-align:center; ">GOODBYE</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:center; ">ABC4567</td><td style="text-align:center; ">NO</td><td style="text-align:center; ">GOODBYE</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:center; ">ABC789</td><td style="text-align:center; ">YES</td><td style="text-align:center; ">HI</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:center; ">ABC789</td><td style="text-align:center; ">YES</td><td style="text-align:center; ">HI</td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href

 

[VoG]

Try

Sub atest()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    Range("E" & Rows.Count).End(xlUp).Offset(1).Resize(Range("D" & i).Value) = Range("A" & i).Value
    Range("F" & Rows.Count).End(xlUp).Offset(1).Resize(Range("D" & i).Value) = Range("B" & i).Value
    Range("G" & Rows.Count).End(xlUp).Offset(1).Resize(Range("D" & i).Value) = Range("C" & i).Value
Next i
End Sub

 

[VoG]

Or better

Sub atest()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    Range("E" & Rows.Count).End(xlUp).Offset(1).Resize(Range("D" & i).Value, 3) = Range("A" & i).Resize(, 3).Value
Next i
End Sub

 

[Dazzawm]

Thanks VoG, nice one.

 

[Dazzawm]

What if I want to put them on sheet 2 rather than next to them on sheet 1?

 

[VoG]

Try

Sub atest()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(Range("D" & i).Value, 3) = Range("A" & i).Resize(, 3).Value
Next i
End Sub

 

[Dazzawm]

Thanks again VoG.

 

[Dazzawm]

I need some tweaking to this code please VoG in post 8. I have inserted another couple of columns so I need all the data in columns A-E copied across the amount of times I have listed in column F onto sheet 2 please. Thanks.