Macro to copy range & paste below successive range

[Alex O]

I need a macro that will copy the text in A3:A9 then add 8 rows and paste the text below every number in range A10:A497. *8 rows because I want one blank row between each account.

Any assistance is appreciated.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 121px"><COL style="WIDTH: 79px"><COL style="WIDTH: 89px"><COL style="WIDTH: 89px"><COL style="WIDTH: 89px"><COL style="WIDTH: 89px"><COL style="WIDTH: 89px"><COL style="WIDTH: 89px"></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><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Accounts Listed</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Amount Listed</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Avg Balance</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Dollars Recovered</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Recovery %</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Cancel Return</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Active & Paid</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">01001494</TD><TD style="TEXT-ALIGN: left">0</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">01001497</TD><TD style="TEXT-ALIGN: left">0</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">01001498</TD><TD style="TEXT-ALIGN: left">0</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">01001501</TD><TD style="TEXT-ALIGN: left">0</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">01001502</TD><TD style="TEXT-ALIGN: left">0</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">01001504</TD><TD style="TEXT-ALIGN: left">0</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">01001991</TD><TD style="TEXT-ALIGN: left">0</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">01003193</TD><TD style="TEXT-ALIGN: left">0</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">01004030</TD><TD style="TEXT-ALIGN: left">0</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">01004056</TD><TD style="TEXT-ALIGN: left">0</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">01006382</TD><TD style="TEXT-ALIGN: left">0</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>

 

[Boswell]

This worked on my one test try.. It takes a while (due to row insert) so I added text to the status bar to track the progress.

Sub insertValues()
 
    Dim i As Long
    Dim copyValues As Variant
    Dim xlCal As Variant
 
    With Application
        xlCal = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
 
    'Get values to copy
    copyValues = Range("A3:A9").Value
 
    i = 11
    Do
        Range("A" & i).EntireRow.Resize(8).Insert
        Range("A" & i & ":A" & i + 6).Value = copyValues
         i = i + 9
        Application.StatusBar = "Performing Routine on Row " & i
    Loop While i <= 9 * 488 + 2
 
    With Application
        .Calculation = xlCal
        .ScreenUpdating = True
        .StatusBar = False
    End With
 
End Sub

 

[Alex O]

That was perfect....took all of about 3 secs. Thanks!