Solved: Move and delete row

[slamet Harto]

Hi there,

I want to move/copy a row if a cell containing a value "Bid Won Declared" or "Not Won" then copy a row to the sheet destination and delete a row in sheet source.


This is what I've done so far:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sms As String

On Error GoTo errHandler

sms = "Are you sure to move and delete this row?"

If Target.Column = 17 Then
If Target.Value = "Bid Won Declared" Then
Cells(ActiveCell.Row, 1).Resize(1, 20).Select

If MsgBox(sms, vbQuestion + vbYesNo, "Please confirm") = vbNo Then Exit Sub
Selection.Copy Worksheets("Bid Won Declared").Range("A" & Rows.Count).End(xlUp).Row
'Selection.EntireRow.Delete
If Target.Value = "Not Won" Then
Cells(ActiveCell.Row, 1).Resize(1, 20).Select

If MsgBox(sms, vbQuestion + vbYesNo, "Please confirm") = vbNo Then Exit Sub
Selection.Copy Worksheets("Not Won").Range("A" & Rows.Count).End(xlUp).Row
'Selection.EntireRow.Delete
End If
End If
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
If Err.Number = 13 Or Err.Number = 1004 Then
GoTo exitHandler
Else
Resume Next
End If
End Sub

Please Kindly help me.
Thanks & Rgds, Harto

[Bob Phillips]

This would be a batch process not event driven



Public Sub MoveRows()
Dim sms As String
Dim LastRow As Long
Dim i As Long

sms = "Are you sure to move and delete this row?"

With Worksheets("prospect")

LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row

For i = LastRow To 38 Step -1

If .Cells(i, "Q").Value = "6 - Bid Won Declared" Then

If MsgBox(sms, vbQuestion + vbYesNo, "Please confirm") = vbNo Then Exit Sub

.Rows(i).Copy Worksheets("Bid Won Declared").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
.Rows(i).Delete

ElseIf .Cells(i, "Q").Value = "1 - Not Win" Then

If MsgBox(sms, vbQuestion + vbYesNo, "Please confirm") = vbNo Then Exit Sub

.Rows(i).Copy Worksheets("Bid Not Won").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
.Rows(i).Delete
End If
Next i
End With
End Sub

[slamet Harto]

Dear Bob,
Thanks for the quick response.

User must choice one by one of the list "reason" In col Q.
I use data validation to cover the list reason.

I can't figure out the code given and combine with my prev code.
I want to use worksheet change in ws modul.

can you help me, please.
Thanks in advance.
Rgds, harto

[slamet Harto]

Dear Bob,

Finally, I can solve this case based on your sugestion.

Thank you for your support and assistance
Rgds, Harto