Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns(7)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
If Target.Value = "Yes" Then
Target.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
Target.EntireRow.Delete
Sheet2.Columns.AutoFit
End If
End Sub