Not entirely sure why you want to break up your data, but you can use the Worksheet_Change()
event in VBA to accomplish what you're after.
This goes in the Completed Worksheet's Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'This goes into your "Completed" worksheet's module
Dim RngB As Range
Set RngB = Intersect(Target, Range("B:B"))
If RngB Is Nothing Then Exit Sub
Application.EnableEvents = False
Dim cel As Range, wsInProgress As Worksheet, retVal As Variant
'!!! Change the worksheet name to whatever it is that moves from your completed
'worksheet to the in-progress worksheet...
Dim wsInProgress As Worksheet
Set wsInProgress = ThisWorkbook.Worksheets("In-Progress")
For Each cel In RngB.Cells
Debug.Print cel.Address
If cel.Value = "In-Progress" Then
wsInProgress.Rows(nextrow(wsInProgress)) = cel.EntireRow.Value
cel.EntireRow.Delete
End If
Next
Application.EnableEvents = True
End Sub
This goes in the In-Progress Worksheet's Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'This goes into your "In-Progress" worksheet's module
Dim RngB As Range
Set RngB = Intersect(Target, Range("B:B"))
If RngB Is Nothing Then Exit Sub
Application.EnableEvents = False
Dim cel As Range, wsInProgress As Worksheet, retVal As Variant
'!!! Change the worksheet name to whatever it is that moves from your completed
'worksheet to the in-progress worksheet...
Dim wsCompleted As Worksheet
Set wsCompleted = ThisWorkbook.Worksheets("Completed")
For Each cel In RngB.Cells
Debug.Print cel.Address
If cel.Value = "Completed" Then
wsInProgress.Rows(nextrow(wsCompleted)) = cel.EntireRow.Value
cel.EntireRow.Delete
End If
Next
Application.EnableEvents = True
End Sub
This goes into a Standard Module:
Public Function nextRow(ByVal ws As Worksheet, Optional ByVal col As Variant = 1) As Long
nextRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row + 1
End Function