Macros to transfer specific data from one sheet to another when a condition is met

[IsaDiaz]

Hi everyone!
I hope you're all doing great

Could you please help me with this?

I need a macros that helps me move from tab 1 (OPEN REQUESTS) to tab 2 (COMPLETED REQUESTS) the records that have a date under the Completed Date column, but I need these records to be automatically removed from the first tab once the date is placed, and moved into the "COMPLETED" tab. Is this possible?

Is it necessary/Should I add a column where it's marked as "Completed" or something?

Thanks a lot for the help!

 

[Michael M]

Maybe this

Sub MM1()
Dim lr As Long, r As Long
Application.screenudating = False
lr = Sheets("COMPLETED REQUESTS").Cells(Rows.Count, "A").End(xlUp).Row + 1
For r = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
    If Cells(r, "F") <> "" Then
        With Rows(r)
            .Copy Sheets("COMPLETED REQUESTS").Cells(lr, "A")
            .Delete
        End With
    lr = lr + 1
    End If
Next r
Application.screenudating = True
End Sub

 

[Michael M]

More efficient

Sub MM3()
 Dim lr As Long
 lr = Sheets("COMPLETED REQUESTS").Cells(Rows.Count, "A").End(xlUp).Row + 1
With Sheets("OPEN REQUESTS").UsedRange
            .AutoFilter
            .AutoFilter field:=6, Criteria1:="<>"
            .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy Sheets("COMPLETED REQUESTS").Cells(lr, "A")
            .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            .AutoFilter
    End With
End Sub

 

[IsaDiaz]

More efficient

Sub MM3()
 Dim lr As Long
 lr = Sheets("COMPLETED REQUESTS").Cells(Rows.Count, "A").End(xlUp).Row + 1
With Sheets("OPEN REQUESTS").UsedRange
            .AutoFilter
            .AutoFilter field:=6, Criteria1:="<>"
            .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy Sheets("COMPLETED REQUESTS").Cells(lr, "A")
            .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            .AutoFilter
    End With
End Sub

Thank you! This worked perfectly, thanks a lot!

 

[Michael M]

Glad to help..