Copy and Paste VBA Help

[shivam03]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thanks for taking time to look at my issue.<o></o>
<o></o>
I have some VBA code which copies a row of data across many columns and pastes is across many rows.<o></o>
<o></o>
I have issue is that VBA cannot handle the amount of data I am asking to paste.<o></o>
<o></o>
I would like to rewrite the code so it does one column at a time and five columns at a time. I am thing a loop might be the solution.<o></o>
<o></o>
I have pasted the code below. lngLastRow is variable that changes a lot. I don't think it handle being greater than 650. The columns are currently between B and BF.<o></o>
<o></o>
Dim lngLastRow As Long<o></o>
lngLastRow = Sheets("#Sheetnames2").Cells(Rows.Count, "C").End(xlUp).Row<o></o>
<o> </o>
Sheets("#Deal Banding Structure (1)").Range("B2:BF2").Copy _<o></o>
Destination:=Sheets("#Deal Banding Structure (1)").Range("B2:BF" & lngLastRow)<o></o>
<o></o>
If anybody has any solutions, that would be much appreciated.<o></o>
<o> </o>
Thanks a lot,<o></o>
<o></o>
Shivam

 

[JackDanIce]

Based on the code below, try:

Dim lngLastRow as Long
lngLastRow = Sheets("#Sheetnames2").Range("C" & Rows.Count).End(xlUp).Row
With Sheets("#Deal Banding Structure(1)")
  .Range("B2:BF" & lngLastRow).FillDown
End With

 

[shivam03]

Thanks for your suggestion. Much appreciated.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
However, I get a Run-time error '9':<o></o>
<o></o>
Subscript out of range.<o></o>
<o></o>
The line that starts with "with" is highlighted in yellow.<o></o>
<o></o>
Any suggestions?<o></o>
<o></o>
Thanks,<o></o>
<o></o>
Shivam<o></o>

 

[shivam03]

I was wondering if anyone has any other ideas to help me with this.

Thanks,

Shivam