Automated excel functions

You don't have to use VBA for this; you can achieve it with normal array formulas using the standard method of INDEX/SMALL etc. For example:

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(Sheet1!$D:$D="wip",ROW(Sheet1!$D:$D)),ROWS($A$2:$A2))),"")

See attached.

WBD

Strange because VBA is much more restrictive (doesn't work online or on mobile). However, right click the sheet with the data, select "View Code" and paste the following:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lastRow As Long

' Only do this if column D is changed
If Application.Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub

' Turn off screen updating
Application.ScreenUpdating = False

' Find the last row of data
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

' Filter the rows for "wip"
Range(Range("A1"), Cells(lastRow, "G")).AutoFilter Field:=4, Criteria1:="wip"

' Clear the contents of the second sheet
Sheets("Sheet2").Cells.ClearContents

' Copy visible cells to the second sheet
Range(Range("A1"), Cells(lastRow, "B")).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Sheet2").Range("A1")

' Remove the filter
Range(Range("A1"), Cells(lastRow, "G")).AutoFilter

' Turn on screen updating
Application.ScreenUpdating = True

End Sub

Also attached.

WBD