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 get the following Run-time error '1004':

Selection is too large.

I got to work just now for nine columns with 10 rows of data.

It does not work form B2 to BF2.

I have some worksheets where I require to be work for an even longer range.

 

[Smitty]

Can you post the code you're using that's causing the error?

 

[shivam03]

Sure. lngLastRow creates the number of rows that pasted downwards.

Thanks.


Dim lngLastRow As Long

lngLastRow = Sheets("#Sheetnames2").Cells(Rows.Count, "C").End(xlUp).Row


Sheets("#Deal Banding Structure (1)").Range("B2:BF2").Copy _
Destination:=Sheets("#Deal Banding Structure (1)").Range("B2:BF" & lngLastRow)

 

[Smitty]

That code shouldn't cause an error, no matter how many times you copy it.

Does it error out on a specific line? If so, which line?

 

[shivam03]

It does not copy anything at all when the number is greater then about 650.

It works for fine when the number is lower.

Does it matter what the contents of the cells are? I have formulas in each one.

Thanks,

shivam03

 

[Smitty]

It shouldn't just stop unless one of your parameters is telling to.

Without seeing the code in its entirety, it's hard to say what's wrong, but you can try clearing the clipboard each time with Application.CutCopyMode = False, but I doubt that's it, since you're only copying a single range.

 

[shivam03]

Your idea did not work but thanks for the suggestion.

I tried testing the code in a new workbook sheet1 with just numbers and it worked fine.

I tried adding in formulas like the ones in my main workbook and it failed again. The formulas are quite long. e.g.

=INDIRECT(ADDRESS(MyEval("="&"MATCH("&"""COMMENTS"""&","&"'"&A2&"'"&"!A:A,0)")-2,3,,,A2))


I get the error selection is too large.

Do you think it is because the formulas are too long?

I used this code in the new workbook.

Sub test()
Dim lngLastRow As Long

lngLastRow = 500

Application.CutCopyMode = False

Sheets("Sheet1").Range("B2:BF2").Copy _
Destination:=Sheets("Sheet1").Range("B2:BF" & lngLastRow)
End Sub

 

[Smitty]

OK, for starters, that code's nothing like what we've been talking about at all. Not even close.

It's possible that you've blowing up what your workbook can handle by copying that many indirects, as it a very pricey formula. And the more complicated you make the formula the more overhead it takes up.

The code posted by itself can handle 65536 rows with no problem on my machine.

Note that you want CutCopyMode at the end.

 

[shivam03]

Okay, I guess you are right trying to copy such long formuals is the problem as it does work with simple formulas and numbers.

What did your code copy and paste on your machine? Was it just numbers in the worksheet?

Do you have any suggestions to overcome this? All I can think of is that I copy and paste a few columns at a time.

Thanks,

shivam03

 

[Smitty]

I copied =ROW()&COLUMN()

You might want to try copying, but turn off Calculation first and see what that does. If all those Indirects are taking up too much overhead while you're doing this then turning them off might help.