Move row data to another sheet based on cell value

[nickbohlender]

I am working on hotel housekeeping boards on Excel 2016. Here is an example: I would like all of the "due out" to be moved to a new sheet with the other info in the row.

151	Due Out	350
152	Stayover	375
153	Due Out	325

<tbody>
</tbody>

 

[My Aswer Is This]

Assuming Due Out is in column B
Try this:

Sub Auto_Filter_This_New()
Application.ScreenUpdating = False
'Modified  12/14/2018  1:40:17 PM  EST
On Error GoTo M
Dim ans As String
ans = "Due Out"
Sheets.Add(After:=Sheets(Sheets.Count)).Name = ans

Sheets(1).Rows(1).Copy Sheets(ans).Rows(1)
Lastrow = Sheets(1).Cells(Rows.Count, "B").End(xlUp).Row
Lastrowa = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    With Worksheets(1).Rows("1:" & Lastrow)
        .AutoFilter
        .AutoFilter Field:=2, Criteria1:="Due Out"
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Worksheets(ans).Range("A" & Lastrowa)
       
    
    End With
    
Sheets(1).AutoFilterMode = False
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "We had some type problem"
End Sub