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

 

[Smitty]

You should stick to your original thread, especially if there's meaningful content there, like your code.

 

[shivam03]

Okay, sure. I will make sure I will do that in the future.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I guess in this case it does not matter that original code is not shown as I qm trying to find an alternative solution based on this simpler example.<o></o>

Hopefully somebody can help.<o></o>
<o></o>
Thanks,<o></o>
<o></o>
shivam03<o></o>

 

[Smitty]

Well, see if this gets you started:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>        x = 10<br>            <SPAN style="color:#00007F">For</SPAN> y = 1 <SPAN style="color:#00007F">To</SPAN> 5<br>                LR = Cells(Rows.Count, "B").End(xlUp).Offset(1).Row<br>                Range("B2:K2").Copy Range("B" & LR & ":K" & LR).Resize(10)<br>            <SPAN style="color:#00007F">Next</SPAN> y<br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[shivam03]

Hi there,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Thanks for this.<o></o>
<o></o>
I think this is not exactly what I am looking for but I could be wrong.<o></o>
<o></o>
I would like the code to copy and paste cells B2 to F2 downwards by 10 rows.<o></o>
<o></o>
After this G2 to K2 downwards by 10 rows.<o></o>
<o></o>
Eventually I would like to make it look so it can do the same for the next five columns, e.g. L to P and so on. That is why I think a loop would be perfect.<o></o>
<o></o>
I will make the 10 rows a variable eventually.<o></o>
<o></o>
Please let me know if you any ideas.<o></o>
<o></o>
Thanks so much, your time and help is much appreciated.<o></o>
<o></o>
shivam03 <o></o>
<o> </o>

 

[Smitty]

Did you try it?

It copies G2:K2 down 5 times in groups of 10 rows each.

 

[shivam03]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I did try it. I think I probably have not explained what I needed very well, sorry.<o></o>
<o></o>
I need to copy the data in cells B2 to F2 downwards by 10 rows and then cells G2 to K2 downwards by 10 rows and then stop.<o></o>
<o></o>
There should only be 10 rows of data in the end.<o></o>
<o></o>
I want the loop to work so I can choose how many groups of 5 columns to copy and paste data downwards.<o></o>
<o> </o>
So if I choose three, it would do B2 to F2, then G2 to K2 and then finally L2 to P2.
Thanks,
shivam03

 

[Smitty]

No, you didn't explain that earlier.

Regardless, does the code do what you were asking for in the first post? If so, then it's easy to put in the column breaks and copy 3 separate ranges. But let's see how close the code got to overall need for now.

 

[shivam03]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
The code in the first post does works for about 500 rows. <o></o>
<o></o>
It does not work for 650+ due to the amount of memory I am asking it to hold.<o></o>
<o></o>
I can get it to work when I work in groups of 3-5 columns.<o></o>
<o></o>
That is why I thought a loop would be the quickest way to achieve this.<o></o>
<o></o>
Does that make sense? I can provide more information.<o></o>
<o></o>
Thanks again,<o></o>
<o></o>
shivam03<o></o>
<o> </o>

 

[Smitty]

Are you getting a memory error or does it just run out of gas?