Transfer and Organise Data

1) Right click the sheet2 tab name and select VIEW CODE
2) Paste in the activation macro to the VBA module that appears:

Option Explicit

Private Sub Worksheet_Activate()
Dim LR As Long

    Cells.Clear
    With Sheets("Sheet1")
        .AutoFilterMode = False
        .Rows(1).AutoFilter
        .Rows(1).AutoFilter Field:=4, Criteria1:="="
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        If LR > 1 Then
            .Range("A1").CurrentRegion.Copy Range("A1")
            Columns.AutoFit
            Range("A:D").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes
        Else
            Range("A1") = "no items remaining"
        End If
        .AutoFilterMode = False
    End With
    
End Sub

3) Close the VBEditor and save your workbook.
4) Make some changes/additions to sheet1
5) Activate sheet2 and the table will appear automatically.

6) Go to sheet1 and fill in all the cells in D so there are no empty cells in the table
7) Activate sheet2 and you'll get a message that there are no outstanding items.