Sorry for this but thought I had totally solved my original problem but it seem i still have a way to go.

The code I am using below does what I want it to do except for the fact I want it to paste into a table so it can be sorted.

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
        Sheets("Database").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    End If
Application.CutCopyMode = False

End Sub

thanks for any help

Processchip