Copy/Paste based on cell value

[bustakaps9]

I'm having trouble sheet pictured below. I'm trying to copy and paste each row "x" number of times, based on the value in column D.

Any help is GREATLY appreciated, as I don't even know where to start with this!

 

[Smitty]

Welcome to the Board!

You'll need VBA for that (but it's easy). Where do you want the copied rows to be pasted?

 

[bustakaps9]

Just to sheet 2 would be great. Thanks!

 

[Smitty]

See if this gets you started:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range([A2], Cells(Rows.Count, "A").End(xlUp))<br>            <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Cells(c.Row, "D").Value<br>                Cells(c.Row, 1).EntireRow.Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1)<br>            <SPAN style="color:#00007F">Next</SPAN> i<br>        <SPAN style="color:#00007F">Next</SPAN> c<br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[bustakaps9]

Perfect! Thanks so much for your help!!!! Any suggestions on where to go to learn VBA? I'm fairly proficient with excel, but when it comes to macros, unless I can record it (I know that's not the right way), I'm clueless... I'd love to understand what each of those statements in there means.

Thanks again for the help!

 

[Smitty]

You can do a Bingle search for "Free VBA Tutorials" and turn up a lot of stuff. Martin Green's got some good ones at www.fontstuff.com.

You can also find the VBA Developer's Reference on www.msdn.com, but it's pretty technical.

Zack Barresse and I have a few articles here as well.

Another good way is to just lurk the VBA posts and see what's going on, in addition to asking your own questions.

One of the first things to learn about recorded code is that it's often inefficient, so most of the screen navigation calls you can lose, and when you see "Select" followed by "Selection" you can usually get rid of both statements and concatenate the remaining code (it's generally not necessary to select things in order to work with them).