Macro to Copy to Next Open Row of New Worksheet

[DataMgmtAnalyst7]

I am running a code that copies data for a specific criteria and moves it from one worksheet to a new worksheet (Sheet2). However, I am having an issue where if I run the code a second time for new values then it deletes all the previous data from Sheet2 and copies the new data over. So, I am trying to figure out how I can edit the code so that when it paste the values to Sheet2 it always pastes them in the next available row instead of overwriting the existing data?

Sub MoveCompleted()
'
'MoveCompleted Macro
'
Dim lastRow As Long
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    ActiveSheet.Range("$A$1:$AW$" & lastRow).AutoFilter Field:=27, Criteria1:="Yes"
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    Sheets("EFT").Select
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.EntireRow.Delete
    Range("B8165").Select
    ActiveSheet.ShowAllData
    Range("B2").Select
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub MoveCompleted()
'
'MoveCompleted Macro
'
   Dim lastRow As Long
   lastRow = Range("A" & Rows.Count).End(xlUp).Row
   With ActiveSheet
      .Range("A1:AW" & lastRow).AutoFilter 27, "Yes"
      .AutoFilter.Range.Offset(1).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .ShowAllData
   End With
End Sub

 

[DataMgmtAnalyst7]

Thank you for quick response. This code successfully solved the issue I was having.

 

[Fluff]

You're welcome & thanks for the feedback.