vba - Copyiny a Row to a new sheet when completed, based on 'Yes' being in column Y...Excel 2010

Question

Please can someone edit or give me code that allows the whole row to be copied to completed work sheet based on column Y having 'Yes' in it and deleting the previous row in register once moved, much appreciated

Sub MoveCompletedProjects()
       Const sCol$ = "Y" '<< search  in col. Y
       Const sCrit$ = "Yes" '<< criteria in col. Y
       Dim ws As Worksheet, ws1 As Worksheet
       Set ws = Sheets("Service Transition Register") '<< source sheet name
       Set ws1 = Sheets("Completed Projects") '<< target sheet name
       Dim r As Long, L As Long
       L = ws1.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
       Application.ScreenUpdating = False
       ws.AutoFilterMode = False
       r = ws.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
       If WorksheetFunction.CountIf(ws.Range(sCol & ":" & sCol), sCrit) > 0 Then '
       ws.Cells(1, sCol).Resize(r).AutoFilter Field:=1, Criteria1:=UCase(sCrit)
       ws.Rows(2 & ":" & r).SpecialCells(xlCellTypeVisible).Copy
       With ws1.Cells(L + 1, 1)
       .PasteSpecial Paste:=xlPasteFormats
       .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        End With
        Application.CutCopyMode = False
        ws.AutoFilterMode = False
        End If
        Application.ScreenUpdating = True
        End Sub

Answer 1

This is a basic code to filter_copy_Paste from one worksheet in the same workbook to another then delete the data from the first workbook. You will need to change the worksheets and ensure the worksheets are in the workbook that has the macro. Comments are in the macro. If your data extends beyond Col "Y", then change the "Y".

'Define your ws variables, change "ThisWorkbook" if sheets are not in the workbook that contains this code
Dim srcws   As Worksheet: Set srcws = ThisWorkbook.Sheets("Sheet1") 'Change sheet names as needed
Dim destws  As Worksheet: Set destws = ThisWorkbook.Sheets("Sheet2")

'Define your range to copy; change "Y" to the last column with data
Set Rng = srcws.Range("A1:Y" & srcws.Range("A" & srcws.Rows.Count).End(xlUp).Row)

With Rng
    srcws.AutoFilterMode = False  'Clear sheet of any current filters
    .AutoFilter 25, "Yes"    'Filter for "Yes" in Col "Y"

    With .Offset(1).SpecialCells(xlCellTypeVisible)  'Offset ensures Header row is not copied. SpecialCells ensures only visible data will be copied
        .Copy Destination:=destws.Cells(destws.Rows.Count, 1).End(xlUp).Offset(1)  'paste in destination sheet below all data
        .EntireRow.Delete  'Delete visible rows that were copied from Sheet1 
    End With

    srcws.AutoFilterMode = False        'Clear the filter
End With