Loop to copy and paste

[shivam03]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thanks for taking time to read this. <o></o>
<o></o>
I have thought of solution to a previous question I posted but need some help to get it started. I have some code that copies and pastes a row of data downwards. The issue is that when I get the code to copy the contents of cells B2 to BF2 downwards by more than 650 rows it does not work.<o></o>
<o> </o>
Please can someone help me with the following -<o></o>
<o></o>
I have the following values in cells B2 to K2.<o></o>
<o></o>
1 =B2 =C2 =D2 =E2 =F2 =G2 =H2 =I2 =J2 =K2<o></o>
<o></o>
I would like to have the contents of cells B2 to F2 copied down by 10 rows and then cells G2 to K10 by 10 rows.<o></o>
<o></o>
I essentially want to have a loop that will do this a number of times. E.g. I might need to copy 50 columns in groups of 5.<o></o>
<o> </o>
Eventually I want to have the number of rows it copies downwards as a variable but will keep it simple for now.<o></o>
<o> </o>
Thanks a lot<o></o>
<o></o>
shivam03

 

[shivam03]

I do have calculation of when I do it and it does not help.



I have started to write a loop that work in columns of five. Is there a way of using my old code to replace the second loop (with k).

I want to copy and paste all the rows in one go, not row by row.

Thanks.


Dim k As Long
k = 1
For J = 0 To 15

Sheets("Sheet1").Select
Range("B2:F2").Offset(0, J).Copy

For k = 1 To 10
Range("B2").Offset(k, J).Select
ActiveSheet.Paste
Next k

k = 1
J = J + 4

Next J

End Sub

 

[shivam03]

I just realised that I should have specified what I meant by my old code. I have pasted it below.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Sub test()
Dim lngLastRow As Long

lngLastRow = 500

Sheets("Sheet1").Range("B2:BF2").Copy _
Destination:=Sheets("Sheet1").Range("B2:BF" & lngLastRow)
End Sub <o></o>
<o></o>
In my new code I was to see if I can do .Copy and then paste the data accross the all the cells underneath not row by row like the code above.<o></o>
<o></o>
Thanks a lot<o></o>
<o></o>
shivam03<o></o>