Macro to Paste Selection Every 12 Rows

[garygraham]

I'm new to macros but trying to learn. Thanks in advance for your help.

I'm trying to copy a range of cells A3:G3 (with formulas) and then paste it every 12 rows to the end of the data which is row 67,789. In other words the range A3:G3 would be pasted into A15, A27, A39, A51, etc. all the way to A67,789.

Thanks,
Gary

 

[Blade Hunter]

If you have no other data in there you can simply do this:

Sub CopyDown()
    Range("A3:G15").AutoFill Destination:=Range("A3:G" & Rows.Count), Type:=xlFillDefault
End Sub

If there is other data you don't wish to overwrite you can do this:

Sub CopyDown2()
Dim X As Long
For X = 15 To Rows.Count Step 12
    Range("A" & X & ":G" & X).Formula = Application.Transpose(Application.Transpose(Range("A3:G3")))
Next
End Sub

 

[garygraham]

There is data in rows 2, 14, 28, 30, etc. but not in the desired paste locations.

Thanks Blade I'll give it a try.

Gary

 

[Blade Hunter]

Go with the second one, it's safer It will take about 30 seconds though so don't worry when your computer appears to be doing nothing

 

[garygraham]

That didn't work as planned.
It pasted values instead of the formulas.
Also, it only copied data from row 3 to rows, 15, 27, 39, etc.
It did not copy data from rows 4 - 13 to rows 16 - 25, 28 - 37, etc.

Need to copy grey highlighted area including formulas to blank areas.


-- removed inline image ---

 

[garygraham]

<table width="512" border="0" cellpadding="0" cellspacing="0"><col style="width:48pt" span="8" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" width="64" height="20">SAPCo</td> <td style="width:48pt" width="64">Segment</td> <td style="width:48pt" width="64">Version </td> <td style="width:48pt" width="64">SAPCCenter</td> <td style="width:48pt" width="64">SAPPCenter</td> <td style="width:48pt" width="64">SAPGroupAcct</td> <td style="width:48pt" width="64">SAPAcct</td> <td style="width:48pt" width="64">Fisc Period</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">1000</td> <td align="right">1000</td> <td>BUDGET</td> <td>
</td> <td align="right">1010100</td> <td align="right">400000</td> <td align="right">404000</td> <td align="right">2011001</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">1000</td> <td class="xl65" align="right">1000</td> <td class="xl65">BUDGET</td> <td class="xl65"></td> <td class="xl65" align="right">1010100</td> <td class="xl65" align="right">400000</td> <td class="xl65" align="right">404000</td> <td align="right">2011002</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">1000</td> <td class="xl65" align="right">1000</td> <td class="xl65">BUDGET</td> <td class="xl65"></td> <td class="xl65" align="right">1010100</td> <td class="xl65" align="right">400000</td> <td class="xl65" align="right">404000</td> <td align="right">2011003</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">1000</td> <td class="xl65" align="right">1000</td> <td class="xl65">BUDGET</td> <td class="xl65"></td> <td class="xl65" align="right">1010100</td> <td class="xl65" align="right">400000</td> <td class="xl65" align="right">404000</td> <td align="right">2011004</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">1000</td> <td class="xl65" align="right">1000</td> <td class="xl65">BUDGET</td> <td class="xl65"></td> <td class="xl65" align="right">1010100</td> <td class="xl65" align="right">400000</td> <td class="xl65" align="right">404000</td> <td align="right">2011005</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">1000</td> <td class="xl65" align="right">1000</td> <td class="xl65">BUDGET</td> <td class="xl65"></td> <td class="xl65" align="right">1010100</td> <td class="xl65" align="right">400000</td> <td class="xl65" align="right">404000</td> <td align="right">2011006</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">1000</td> <td class="xl65" align="right">1000</td> <td class="xl65">BUDGET</td> <td class="xl65"></td> <td class="xl65" align="right">1010100</td> <td class="xl65" align="right">400000</td> <td class="xl65" align="right">404000</td> <td align="right">2011007</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">1000</td> <td class="xl65" align="right">1000</td> <td class="xl65">BUDGET</td> <td class="xl65"></td> <td class="xl65" align="right">1010100</td> <td class="xl65" align="right">400000</td> <td class="xl65" align="right">404000</td> <td align="right">2011008</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">1000</td> <td class="xl65" align="right">1000</td> <td class="xl65">BUDGET</td> <td class="xl65"></td> <td class="xl65" align="right">1010100</td> <td class="xl65" align="right">400000</td> <td class="xl65" align="right">404000</td> <td align="right">2011009</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">1000</td> <td class="xl65" align="right">1000</td> <td class="xl65">BUDGET</td> <td class="xl65"></td> <td class="xl65" align="right">1010100</td> <td class="xl65" align="right">400000</td> <td class="xl65" align="right">404000</td> <td align="right">2011010</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">1000</td> <td class="xl65" align="right">1000</td> <td class="xl65">BUDGET</td> <td class="xl65"></td> <td class="xl65" align="right">1010100</td> <td class="xl65" align="right">400000</td> <td class="xl65" align="right">404000</td> <td align="right">2011011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">1000</td> <td class="xl65" align="right">1000</td> <td class="xl65">BUDGET</td> <td class="xl65"></td> <td class="xl65" align="right">1010100</td> <td class="xl65" align="right">400000</td> <td class="xl65" align="right">404000</td> <td align="right">2011012</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">1000</td> <td align="right">1000</td> <td>BUDGET</td> <td>
</td> <td align="right">1010200</td> <td align="right">400000</td> <td align="right">404000</td> <td align="right">2011001</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011002</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011003</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011004</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011005</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011006</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011007</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011008</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011009</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011010</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011011</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011012</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">1000</td> <td align="right">1000</td> <td>BUDGET</td> <td>
</td> <td align="right">1010700</td> <td align="right">400000</td> <td align="right">404000</td> <td align="right">2011001</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011002</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011003</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011004</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011005</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011006</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011007</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011008</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011009</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011010</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011011</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011012</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">1000</td> <td align="right">1000</td> <td>BUDGET</td> <td>
</td> <td align="right">1010800</td> <td align="right">400000</td> <td align="right">404000</td> <td align="right">2011001</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011002</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011003</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011004</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011005</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011006</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011007</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011008</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011009</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011010</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011011</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td align="right">2011012</td> </tr> </tbody></table>
-- removed inline image ---

 

[Blade Hunter]

Sub CopyDown()
    Range("A3:G15").AutoFill Destination:=Range("A16:G" & Rows.Count), Type:=xlFillDefault
End Sub

Try that, I thought from your original post you only wanted Row 3 copying.

 

[garygraham]

Sorry to be a pain but now I get a
Run Time Error "1004":
AutoFill method of Range class failed

 

[garygraham]

I think the ranges should be

Range("A3:G13").AutoFill Destination:=Range("A15:G" & Rows.Count), Type:=xlFillDefault

but it still failed with the same run time error.

Thanks,
Gary

 

[Blade Hunter]

My Mistake, try this:

Sub CopyDown()
    Range("A3:G14").AutoFill Destination:=Range("A3:G" & Rows.Count), Type:=xlFillDefault
End Sub

It will take a little while depending on how many rows in the sheet.