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".



In article <58656529acd2d@uwe, "DWC via OfficeKB.com" <u16385@uwe
wrote:

Hi

Firstly, thanks for your consideration.

I have a workbook with two worksheets. I wish to set up an automation that
cuts a row from sheet 1, pastes that row into row one of sheet 2 then deletes
the now blank row from sheet 1.

The trigger for this event is when users select the string - "3. Finalised" -
made available from a validation list in column H of sheet 1. What I wish to
happen at that stage is for a msgbox to automatically appear advising the
user that if they click "OK" in the msgbox, the row will be transferred (as
described above) and that, should they wish for this NOT to happen, then they
must exit (ESC?) and select another string other than
"3. Finalised".

I hope this makes sense and I kindly ask for your wisdom.

BTW, I have been trying to get my head around VBA (absolute novice) but am
finding it difficult - if you can share a link that is like "VBA for Dummies"
I would be much appreciated.

Many thanks in advance...