Move based on value VBA

Simply copying the row causes formulas (like the one in column D) to re-point to the wrong cells on the destination sheet.

It is the best-practice, production-ready version of your macro.
It avoids Select, handles row deletion correctly, preserves your logic, and locks column D as values before moving the row so formulas never shift.

Sub MoveCompletedProjects()

    Dim wsSrc As Worksheet
    Dim wsDst As Worksheet
    Dim lastSrcRow As Long
    Dim lastDstRow As Long
    Dim r As Long

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    Set wsSrc = Worksheets("Deliverables")
    Set wsDst = Worksheets("Complete")

    ' Last used rows
    lastSrcRow = wsSrc.Cells(wsSrc.Rows.Count, "S").End(xlUp).Row
    lastDstRow = wsDst.Cells(wsDst.Rows.Count, "A").End(xlUp).Row

    ' Loop bottom-to-top to safely delete rows
    For r = lastSrcRow To 1 Step -1
        
        If wsSrc.Cells(r, "S").Value = "Complete" Then
            
            ' Lock column D as values BEFORE moving
            wsSrc.Cells(r, "D").Value = wsSrc.Cells(r, "D").Value
            
            ' Copy row to destination
            wsSrc.Rows(r).Copy Destination:=wsDst.Rows(lastDstRow + 1)
            
            ' Delete source row
            wsSrc.Rows(r).Delete
            
            lastDstRow = lastDstRow + 1
        End If
        
    Next r

CleanExit:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

End Sub

No .Select or .Activate

Bottom-to-top loop

Formula-safe transfer

Performance optimized

Explicit worksheet references

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.