Try an alternative

Code:
Sub TrimWorkbook()

    Dim colLetter As String, SavePath As String
    Dim lastValue As String
    Dim wb As Workbook
    Dim lng As Long
    Dim currentRow As Long
    colLetter = "K"
    SavePath = ThisWorkbook.Path
    'Sort the workbook.
    With ThisWorkbook.Worksheets(1)
        .Cells.AutoFilter field:=.Cells(1, colLetter).Column, Criteria1:="Y"
        lng = .Cells(.Rows.Count, colLetter).End(xlUp).Row
        Set wb = Application.Workbooks.Add(xlWorksheet)
        .Rows(1 & ":" & lng).Copy wb.Sheets(1).Cells(Rows.Count, 1).End(xlUp)
        wb.SaveAs SavePath & "\" & "CPC_Weekend_Work_" & Format(Now(), "DD-MMM-YYYY"), 51
        wb.Close
        .AutoFilterMode = False
    End With
    
End Sub