VBA to cut entire row based on text in cell and paste to new sheet

[shanegu01]

Looking to cut content of entire row based on whether cell in column 'D' (date completed) has a date in it. If so, need to lookup date in column 'B' (Date received) and paste content of row into first available row in the worksheet for that month/year.
E.g. row 3 in example below has a date in the 'date completed' cell, so need to cut entire row and lookup to check date in the 'Date received' column (in this case Jan 2020) and paste in worksheet titled 'Jan 2020'. Need to search through each row and do the same function.

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this in a copy of your workbook. It assumes that any required monthly sheets already exists.

Sub MoveCompleted()
  Dim rw As Range
  
  With Sheets("data").Range("A1").CurrentRegion
    .AutoFilter Field:=5, Criteria1:="<>"
    For Each rw In .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Rows
      rw.Copy Destination:=Sheets(Format(rw.Cells(5), "mmm yyyy")).Range("E" & Rows.Count).End(xlUp).Offset(1, -4)
    Next rw
    .Offset(1).EntireRow.Delete
    .AutoFilter
  End With
End Sub

BTW, for the future, you will faster & better help if you provide your sample data in a format that can be copied for testing. My signature block below has help with that. You would realise that helpers want to help, not type out data.

 

[shanegu01]

Thanks. I give it a try. Much appreciated.

 

[shanegu01]

Hi Peter_SSs

Thanks for the code. It mostly worked. What's missing is the pasting of the rows into the respective worksheets based on the data in column 'C' - Date Received date. The way you made it work is based on the date in column 'E' - Date completed. Is this possible

 

[Peter_SSs]

Ah, yes sorry. I did read that but I was tricked by your image showing only sheets for Jan & Feb 2020 which were the 'Completed' months. To use the date from column C, just make this change

rw.Copy Destination:=Sheets(Format(rw.Cells(3), "mmm yyyy")).Range("E" & Rows.Count).End(xlUp).Offset(1, -4)

But remember those monthly sheets must already exist in the workbook for my code to work as it is at the moment.

 

[shanegu01]

Worked perfectly - sorry for the confusion. My mistake.

 

[Peter_SSs]

Worked perfectly - sorry for the confusion. My mistake.

No problem - seems we got there in the end.