Finding last row of column not recognizing all rows in column

[uvela]

Hello,

I've created a workbook with 4 worksheets that have been copied from other workbooks. In this workbook I'm trying to copy and paste all data from a column in sheet1 to sheet2.

I'm using the following vba code:

Dim lastRow As Long
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    Sheets("sheet1").Activate
    Range("A2:A" & lastRow).Copy
    Sheets("sheet2").Activate
    Range("A2").PasteSpecial Paste:=xlPasteValues

In my case, sheet1 has 63863 rows, but the above code only pastes 32582 rows into sheet2.

Any suggestions or help would be greatly appreciated.

Thanks

 

[mohadin]

Dim lastRow As Long

    Sheets("sheet1").Activate
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A2:A" & lastRow).Copy
    Sheets("sheet2").Activate
    Range("A2").PasteSpecial Paste:=xlPasteValues

 

[Peter_SSs]

Welcome to the MrExcel board!

There is generally no need to "activate" a sheet to work with it & activating is a relatively slow process & can make the screen flicker. It won't make any noticeable difference in what you are doing this time but I would suggest trying to learn to code without activating as much as possible.

Here is another option to try with a copy of your workbook.

Sub CopyData()
  Dim lastRow As Long
  
  With Sheets("Sheet1")
    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    Sheets("Sheet2").Range("A2:A" & lastRow).Value = .Range("A2:A" & lastRow).Value
  End With
End Sub

 

[uvela]

Thank you for both of your replies. They both fixed my problem. Much appreciated.

 

[Peter_SSs]

Glad we could help. Thanks for letting us know.