Thread: Macro is messing up formula values

Okay, so I am so close to having my worksheet where I need it! I have a mortgage pipeline spreadsheet. I'm trying to have the row moved to the booked worksheet when I select booked in column d and for the row to move to the dnmq worksheet whenever dnmq is selected. It sucessfully does move it, but it throws off the formulas in column F. Any ideas on how to have the rows move like now, but for the formulas in column "potential tsv" F to remain correct, i.e. still pertaining to the correct row?

Also, when the row moves to the other worksheet, it deletes the row and moves up data so there is no blank row. I have attached the file and also the code.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 4 And Target.Cells.Count = 1 Then MoveMe Target
End Sub

Sub MoveMe(rTarget As Range)
    Dim wsMoveTo    As Worksheet
    Dim rMoveToRow  As Range
    Dim rCopyFrom   As Range
    Dim rCopyTo     As Range
    Dim rItem       As Range
    Dim rMoveUp     As Range
    Dim bEvents     As Boolean
    Dim bScrUpd     As Boolean
    Dim lCalc       As Long
    
    With Application
        bEvents = .EnableEvents
        bScrUpd = False
        lCalc = .Calculation
        .EnableEvents = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    

    Select Case rTarget.Text
        Case "Booked", "DNMQ"
            Set wsMoveTo = Sheets(rTarget.Text)
        Case Else
            Exit Sub
    End Select
    Set rMoveToRow = wsMoveTo.Cells.Find("Potential", LookIn:=xlValues, LookAt:=xlWhole).End(xlUp).Offset(1)
    Set rCopyFrom = Range(Cells(rTarget.Row, "A"), Cells(rTarget.Row, "J"))
    Set rCopyTo = wsMoveTo.Range(wsMoveTo.Cells(rMoveToRow.Row, "A"), wsMoveTo.Cells(rMoveToRow.Row, "J"))
    rCopyTo.Formula = rCopyFrom.Formula
    Set rMoveUp = Range(Cells.Find("Potential", LookIn:=xlValues, _
        LookAt:=xlWhole).End(xlUp).Offset(1), rCopyFrom)
    rMoveUp.Formula = rMoveUp.Offset(1).Formula

    
    Set wsMoveTo = Nothing
    Set rMoveToRow = Nothing
    Set rCopyFrom = Nothing
    Set rCopyTo = Nothing
    
    With Application
        .EnableEvents = bEvents
        .ScreenUpdating = bScrUpd
        .Calculation = lCalc
    End With
End Sub