Auto copy and paste value

Hi

This is driving me up the wall so I hope someone can help me.

I am using the code below to copy a row from one sheet to another sheet and also lock the original row.

The code works as it should but when the row is pasted into the new sheet it keeps all the formulas etc and I just want the value/text.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Count > 1 Or Target.Column <> 9 Then Exit Sub
    ActiveSheet.Unprotect Password:="mypass"

      If Target.Text = "Yes" Then
         Target.Offset(0, -7).Resize(1, 9).Locked = True
      ActiveSheet.Protect Password:="mypass"
        Target.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
 

End Sub

Can someone help me with the missing code please


Processchip