slamet Harto
09-24-2008, 02:02 AM
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
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