Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim KeyCells As Range
- Set KeyCells = Range("B:B")
- If Not Application.Intersect(KeyCells, Range(Target.Address)) _
- Is Nothing Then
- If (Target.Value = "Complete" Or Target.Value = "On Hold") Then
- ActiveCell.EntireRow.Copy
- Worksheets("Sheet2").Activate
- i = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
- Worksheets("Sheet2").Cells(i + 1, 1).Select
- ActiveSheet.Paste
- Worksheets("Sheet1").Activate
- ActiveCell.EntireRow.Delete
- End If
- End If
- End Sub
- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim KeyCells As Range
- Application.EnableEvents = False ' Disable events while routine is doing its duty
- On Error Goto ChangeExit ' Ensure that events are switched on in any case
- Set KeyCells = Range("B:B")
- If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
- With Target.Cells(1, 1)
- If (.Value = "Complete" Or .Value = "On Hold") Then
- Dim lastRow As Long
- lastRow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).row
- .EntireRow.Copy Worksheets("Sheet2").Cells(lastRow + 1, 1)
- .EntireRow.Delete
- End If
- End With
- End If
- ChangeExit:
- Application.EnableEvents = True
- End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement