In the attached:
Sub blah()
Dim Codes(), RngToCopy As Range
'bring values of the Selection sheet table into memory (an array):
With Sheets("Selection")
SelectionVals = Range(.Range("A3"), .Cells(.Rows.Count, "A").End(xlUp).Offset(, 2))
End With
'create an array of 2-character codes according to 'x' (upper or lower case):
For i = 1 To UBound(SelectionVals)
If UCase(Application.Trim(SelectionVals(i, 3))) = "X" Then
Count = Count + 1
ReDim Preserve Codes(1 To Count)
Codes(Count) = Left(Trim(SelectionVals(i, 1)), 2)
End If
Next i
'determine cells to copy:
If IsEmpty(Count) Then 'means no matching codes/xs then clear the Output sheet data, so just clear the data from the Output sheet:
With Sheets("Output")
.Range("A4:A" & Application.Max(4, .Cells(.Rows.Count, "A").End(xlUp).Row)).Resize(, 4).Clear ' clear the data from the Output sheet
Application.Goto .Range("A1") ' go to the Output sheet
End With
Else 'there are some codes so process them:
With Sheets("Dataset")
Set CodesColm = Range(.Range("A2"), .Cells(.Rows.Count, "A").End(xlUp)) 'column A (Codes) of the Dataset sheet with values.
For Each codecell In CodesColm.Cells
For i = 1 To UBound(Codes) 'run through each of the 2-character codes…
If Left(Trim(codecell.Value), 2) = Codes(i) Then 'checking if they're the same as left 2 characters of the Dataset sheet's codes in column A…
If RngToCopy Is Nothing Then Set RngToCopy = codecell.Resize(, 4) Else Set RngToCopy = Union(RngToCopy, codecell.Resize(, 4)) 'if they are add the 4-column width row to the range to be copied.
End If
Next i
Next codecell
End With
With Sheets("Output")
If Not RngToCopy Is Nothing Then 'check to see that at least one row is to be copied
.Range("A4:A" & Application.Max(4, .Cells(.Rows.Count, "A").End(xlUp).Row)).Resize(, 4).Clear ' clear the data from the Output sheet
RngToCopy.Copy .Range("A4") 'copy the data
Application.Goto .Range("A1") ' go to the Output sheet but only if data there has been updated.
End If '
End With
End If
End Sub
I may look at other ways later (Advanced Filter, Power Query, pivot table)