Excel VBA to Copy paste using with statement to return message box

[Ann Ooi]

I need some advice to rectify the code below. I have this code to copy paste lines to another sheet for data compilation purpose. And I'm running well using the with statement below, the problem is, when there's no data to paste, I do not know how to end the code with message box. I see the similar question above, how to comply the code into the With statement of VBA below? Following is the code I read from other user, to return message box if error. {If Err Then MsgBox "Nothing to paste!" Err.Clear End If}.
Or should I change the coding? If so, what is the correct coding to apply?

My original code, without the Message box return.

[/
Sub FnLstRow()
Application.ScreenUpdating = False
Dim LR As Long
ThisWorkbook.Worksheets("Data").Select
LR = Cells(Rows.Count, "AO").End(xlUp).Row
Cells(LR, 1).Offset(1, 0).EntireRow.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
With Sheets("LatestData")
    .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

End With
Application.CutCopyMode = False


Range("A1").Select
Application.ScreenUpdating = True
End Sub
]

 

[Yongle]

When there is no data to paste ...
... is Cells(LR, 1).Offset(1, 0) empty ?

Sub FnLstRow()
    Application.ScreenUpdating = False
    Dim LR As Long
    ThisWorkbook.Worksheets("Data").Select
    LR = Cells(Rows.Count, "AO").End(xlUp).Row
    Cells(LR, 1).Offset(1, 0).EntireRow.Select
    If Selection.Cells(1, 1) <> "" Then
        Cells(LR, 1).Offset(1, 0).EntireRow.Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        With Sheets("LatestData")
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
        End With
        Application.CutCopyMode = False
        Range("A1").Select
    Else
        MsgBox "No Data", vbExclamation, ""
    End If
End Sub

 

[Ann Ooi]

Hi Yongle,
Yes, you are correct, the row A is header, and the cells below, which is {Cells(LR, 1).Offset(1, 0)} are empty. But I applied the code you stated, it's still not working, it's return the same error, which is something "out of memory etc".

 

[Yongle]

Sub FnLstRow()
    Application.ScreenUpdating = False
    Dim LR As Long, data As Worksheet, latest As Worksheet
    Set data = Sheets("Data")
    Set latest = Sheets("LatestData")
    LR = data.Cells(Rows.Count, "AO").End(xlUp).Row
    If LR > 1 Then
        data.Rows(2).Resize(LR).Copy
        latest.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        latest.Activate
        Range("A1").Select
    Else
        MsgBox "No Data", vbExclamation, ""
    End If
End Sub

 

[Ann Ooi]

Hi Yongle,
Thank you so much, it's working fine now. You are superb!