Maybe this'll be closer:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nResult As Long
Dim DestCell As Range

On Error GoTo errHandler:

With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Me.Columns(8)) Is Nothing Then
If LCase(.Text) Like "*finalized*" Then
nResult = MsgBox(Prompt:= _
"Clicking yes will move this line to sheet 2", _
Title:="Are you sure?", _
Buttons:=vbYesNo)
Application.EnableEvents = False
If nResult = vbYes Then
With Sheets("sheet2")
Set DestCell = .Range("A" & _
.Cells(.Rows.Count, 8).End(xlUp).Row + 1)
End With
With Me.Rows(.Row)
.Copy _
Destination:=DestCell
.Delete Shift:=xlUp
End With
Else
.ClearContents
End If
End If
End If
End With

errHandler:
Application.EnableEvents = True
End Sub

"DWC via OfficeKB.com" wrote:
>
> Hi JE
>
> Many thanks for the prompt reply and based on my description, your code
> worked - cheers!
>
> Now that I have run it, I do need seek advice on tweeking the code as follows:
>
> 1. By clicking "NO" in the message box, the cell that has "3. Finalized" in
> it should clear so that the user knows they must choose another selection
> from the drop down list.
>
> 2. The automated cut and paste to sheet 2 must retain previously pasted rows -
> your code currently overwrites prevous entries - i.e. all finalised rows must
> remain in sheet 2.
>
> JE can you Assist?
>
> JE McGimpsey wrote:
> >One way:
> >
> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > Dim nResult As Long
> > With Target
> > If .Count > 1 Then Exit Sub
> > If Not Intersect(.Cells, Columns(8)) Is Nothing Then
> > If .Text Like "Finalized" Then
> > nResult = MsgBox(Prompt:= _
> > "Clicking yes will move this line to sheet 2", _
> > Title:="Are you sure?", _
> > Buttons:=vbYesNo)
> > If nResult = vbYes Then
> > With Rows(.Row)
> > .Copy Destination:=Sheets("Sheet2").Rows(1)
> > .Delete Shift:=xlUp
> > End With
> > End If
> > End If
> > End If
> > End With
> > End Sub
> >
> >Note that the Worksheet_Change event will not fire when a selection is
> >made from a validation dropdown in XL97 and MacXL (don't remember about
> >XL00). For compatibility with those versions, use the _Calculate event
> >and check your range for "Finalized".
> >
> >> Hi
> >>
> >[quoted text clipped - 19 lines]
> >>
> >> Many thanks in advance...
>
> --
> Message posted via http://www.officekb.com

--

Dave Peterson