What version of xl are you running?

(Read JE's notes if you're running xl97.)

"DWC via OfficeKB.com" wrote:

Hi David

Many thanks for the reply and time taken...

I have inserted yuor code but it does not seem to be triggering anything when
I make the change to sheet1 column 8. I am currently trying a hybrid between
your and JE's codes - will be more specific if I can isolate anything.

Cheers!

Dave Peterson wrote:
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

Hi JE

[quoted text clipped - 46 lines]

Many thanks in advance...


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

--

Dave Peterson