Macro to Copy and paste in interval of 7 rows

[amitcohen]

Hi All
Hope you can help me..

I need to copy a range of cells (A3:J43)
and then to paste it 7 rows under the previous paste.
This need to take place 100 times.

In other words:
Copy 40 rows
Jump 7 rows (from the last cell of the range)
paste the 40 rows
jump 7 rows (from the last cell of the range)
paste 40 row

and so on.. 100 times.

Thank you for your kind help.

Amit

 

[ravishankar]

Hi
Assuming you want to paste the same range 100 times, try the following codes

Sub copyNpaste()
Dim a As Long
Application.ScreenUpdating = False
Sheets("sheet1").Range("A3:J43").Copy
    For a = 1 To 100
    Sheets("Sheet2").Range("A" & (a - 1) * 47 + 1).PasteSpecial
    Next a
Application.ScreenUpdating = True
MsgBox "Copying complete"
end sub

Ravi

 

[amitcohen]

Hi Ravi
This is great!!
Perfect.

Thank you very much for your help

Amit.

 

[Peter_SSs]

Assuming the 6 rows below your copy range are blank, this would be a lot quicker.
Do we need to worry about PasteSpecial?

If you want the original plus 100 copies (that is 101 versions in total) then change the 100 in the code to 101.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Copy_Paste()<br>    <SPAN style="color:#00007F">With</SPAN> Range("A3:J43")<br>        .Resize(.Rows.Count + 6).Copy Destination:=.Resize(100 * (.Rows.Count + 6))<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[amitcohen]

Hi Peter
This is truly amazing!!
Its 100% accurate.
You guys saved me a lot of time.

Many thanks

Amit Cohen