Is there a limit on the number of Excel Rows that VBA can paste into another workbook?

[Jdrew]

I have written a VB macro that copies rows from four Excel worksheets and pastes them in separate sheets in another workbook. One of the worksheets to be copied has 46K rows. When I paste it, it will only paste about 16K rows. Am I hitting some pasting limit? Operative section of the code looks like this

Set ODC = Worksheets("ODC").Range("A:O")

Workbooks(D003Var).Activate

ODC.Copy

Worksheets("ODC").Range("A1").PasteSpecial Paste:=xlPasteValues
Worksheets("ODC").Range("A1").PasteSpecial Paste:=xlPasteFormats

Workbooks(D003Var).Close SaveChanges:=True
Workbooks(BigD003).Close SaveChanges:=False

 

[Trevor G]

Hi JDrew welcome to the form,

From you post you are selecting to copy the entire columns A:O which may cause the issue, if the data is only in column A:O you could consider using select.currentregion.copy which would copy only the amount of rows that has the data in them. This may help you get through this procedure.