Macro to insert and copy rows based on number in column

E

enricopalazzo

New Member
Joined
Jun 3, 2011
Messages
6
I need help writing a macro that can copy and insert x rows based on the number in column N in the first row, then go to the next row and repeat. I have about 10,000 rows of data, and when the macro is done there will be about 60,000 rows. I am using Excel 2007 and run Windows XP.

Thank you in advance.

P.S. I tried attaching a worksheet to this but couldn't get it to work. If it would help, and you can explain how to do it, I would be happy to.

<table style="width: 696px; height: 588px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="14"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">
</td><td style="width: 48pt;" width="64">
</td><td style="width: 48pt;" width="64">
</td><td style="width: 48pt;" width="64">
</td><td style="width: 48pt;" width="64">
</td><td style="width: 48pt;" width="64">
</td><td style="width: 48pt;" width="64">
</td><td style="width: 48pt;" width="64">
</td><td style="width: 48pt;" width="64">
</td><td style="width: 48pt;" width="64">
</td><td style="width: 48pt;" width="64">
</td><td style="width: 48pt;" width="64">
</td><td class="xl78" style="width: 48pt;" width="64">
</td><td style="width: 48pt;" width="64">
</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl77" style="height: 12.75pt;" align="right" height="17">
</td><td>
</td><td>
</td><td>
</td><td class="xl78">
</td><td>
</td><td>
</td><td>
</td><td>
</td> <td class="xl78">
</td> <td>
</td> <td class="xl78">
</td> <td class="xl79" align="right">
</td> <td class="xl77" align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl77" style="height: 12.75pt;" align="right" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl78">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl78">
</td> <td>
</td> <td class="xl78">
</td> <td class="xl79" align="right">
</td> <td class="xl77" align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl77" style="height: 12.75pt;" align="right" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl78">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl78">
</td> <td>
</td> <td class="xl78">
</td> <td class="xl79" align="right">
</td> <td class="xl77" align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td><td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl78">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl77" style="height: 12.75pt;" align="right" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl78">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl78">
</td> <td>
</td> <td class="xl78">
</td> <td class="xl79" align="right">
</td> <td class="xl77" align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl77" style="height: 12.75pt;" align="right" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl78">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl78">
</td> <td>
</td> <td class="xl78">
</td> <td class="xl79" align="right">
</td> <td class="xl77" align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl77" style="height: 12.75pt;" align="right" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl78">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl78">
</td> <td>
</td> <td class="xl78">
</td> <td class="xl79" align="right">
</td> <td class="xl77" align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl77" style="height: 12.75pt;" align="right" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl78">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl78">
</td> <td>
</td> <td class="xl78">
</td> <td class="xl79" align="right">
</td> <td class="xl77" align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl77" style="height: 12.75pt;" align="right" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl78">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl78">
</td> <td>
</td> <td class="xl78">
</td> <td class="xl79" align="right">
</td> <td class="xl77" align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl77" style="height: 12.75pt;" align="right" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl78">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl78">
</td> <td>
</td> <td class="xl78">
</td> <td class="xl79" align="right">
</td> <td class="xl77" align="right">
</td> </tr> </tbody></table>
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
To give a little more detail, I want Excel to copy the original line of data and insert it n-1 times (so that the total number of times each line appears is equal to the number in column N). The end goal is to get the weighted percentiles of the data.
 
Upvote 0
I am slowly figuring all of this out, thank you for your patience. The first 3 rows is how the data currently appear. The bottom portion is how I want it to appear after the macro runs:

Excel 2010<TABLE style="BORDER-BOTTOM: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #dae7f5; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH><TH>N</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>providerId</TD><TD>businessName</TD><TD>lastName</TD><TD>firstName</TD><TD>mAddress</TD><TD>mCity</TD><TD>state</TD><TD>mZip</TD><TD>county</TD><TD>phone</TD><TD>careType</TD><TD>ageGroup</TD><TD>total hours</TD><TD>enrollment</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">237</TD><TD>Pages Discovery Academy - Plain City</TD><TD>Blair</TD><TD>Tauna</TD><TD>xxxxx</TD><TD>Plain City</TD><TD>Utah</TD><TD style="TEXT-ALIGN: right">84404</TD><TD>Weber</TD><TD>xxxxxx</TD><TD>Center Licensed</TD><TD>Infant 0<24 months</TD><TD style="TEXT-ALIGN: right">400.00</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">239</TD><TD>Apple Developmental Daycare</TD><TD>Mardanlou</TD><TD>Jam</TD><TD>xxxxx</TD><TD>Bountiful</TD><TD>Utah</TD><TD style="TEXT-ALIGN: right">84010</TD><TD>Davis</TD><TD>xxxxxx</TD><TD>Center Licensed</TD><TD>Infant 0<24 months</TD><TD style="TEXT-ALIGN: right">475.00</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">240</TD><TD>Bryden Academy Learning and Daycare</TD><TD>Beazer</TD><TD>Denise</TD><TD>xxxxx</TD><TD>West Bountiful</TD><TD>Utah</TD><TD style="TEXT-ALIGN: right">84087</TD><TD>Davis</TD><TD>xxxxxx</TD><TD>Center Licensed</TD><TD>Infant 0<24 months</TD><TD style="TEXT-ALIGN: right">425.00</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>providerId</TD><TD>businessName</TD><TD>lastName</TD><TD>firstName</TD><TD>mAddress</TD><TD>mCity</TD><TD>state</TD><TD>mZip</TD><TD>county</TD><TD>phone</TD><TD>careType</TD><TD>ageGroup</TD><TD>total hours</TD><TD>enrollment</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right">237</TD><TD>Pages Discovery Academy - Plain City</TD><TD>Blair</TD><TD>Tauna</TD><TD>xxxx</TD><TD>Plain City</TD><TD>Utah</TD><TD style="TEXT-ALIGN: right">84404</TD><TD>Weber</TD><TD>xxxxxx</TD><TD>Center Licensed</TD><TD>Infant 0<24 months</TD><TD style="TEXT-ALIGN: right">400.00</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right">239</TD><TD>Apple Developmental Daycare</TD><TD>Mardanlou</TD><TD>Jam</TD><TD>xxxx</TD><TD>Bountiful</TD><TD>Utah</TD><TD style="TEXT-ALIGN: right">84010</TD><TD>Davis</TD><TD>xxxxxx</TD><TD>Center Licensed</TD><TD>Infant 0<24 months</TD><TD style="TEXT-ALIGN: right">475.00</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right">239</TD><TD>Apple Developmental Daycare</TD><TD>Mardanlou</TD><TD>Jam</TD><TD>xxxx</TD><TD>Bountiful</TD><TD>Utah</TD><TD style="TEXT-ALIGN: right">84010</TD><TD>Davis</TD><TD>xxxxxx</TD><TD>Center Licensed</TD><TD>Infant 0<24 months</TD><TD style="TEXT-ALIGN: right">475.00</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right">240</TD><TD>Bryden Academy Learning and Daycare</TD><TD>Beazer</TD><TD>Denise</TD><TD>xxxx</TD><TD>West Bountiful</TD><TD>Utah</TD><TD style="TEXT-ALIGN: right">84087</TD><TD>Davis</TD><TD>xxxxxx</TD><TD>Center Licensed</TD><TD>Infant 0<24 months</TD><TD style="TEXT-ALIGN: right">425.00</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right">240</TD><TD>Bryden Academy Learning and Daycare</TD><TD>Beazer</TD><TD>Denise</TD><TD>xxxx</TD><TD>West Bountiful</TD><TD>Utah</TD><TD style="TEXT-ALIGN: right">84087</TD><TD>Davis</TD><TD>xxxxxx</TD><TD>Center Licensed</TD><TD>Infant 0<24 months</TD><TD style="TEXT-ALIGN: right">425.00</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right">240</TD><TD>Bryden Academy Learning and Daycare</TD><TD>Beazer</TD><TD>Denise</TD><TD>xxxx</TD><TD>West Bountiful</TD><TD>Utah</TD><TD style="TEXT-ALIGN: right">84087</TD><TD>Davis</TD><TD>xxxxxx</TD><TD>Center Licensed</TD><TD>Infant 0<24 months</TD><TD style="TEXT-ALIGN: right">425.00</TD><TD style="TEXT-ALIGN: right">3</TD></TR></TBODY></TABLE>
Sheet2​
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
C
  • Question Question
Macro Help - Transposing Some Data from Columns to Rows, Accounting for Duplicate Data
Replies
1
Views
127
Kaper
Kaper
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
119
Peter_SSs
Peter_SSs
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
105
flashgordie
F
R
  • Question Question
VBA Question - VBA for validating value from multiple cells/column and fetching value another sheet's cell and pasting it another cell of sheet
Replies
2
Views
320
Kaper
Kaper

Forum statistics

Threads
1,224,597
Messages
6,179,808
Members
452,944
Latest member
2558216095

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top