Search Range, List Results in MultiColumn ListBox

The database form example does this, get it here

http://www.excel-it.com/vba_examples.htm

This uses AdvancedFilter to copy the records matching the search term to another sheet. It uses an InputBox to get the search term from the user.

Sub AdvancedFilterMove()
    Dim dataRange As Range
    Dim keyCol As Range, keyValue As Variant
    Dim destinationSheet As Worksheet
    Dim destinationRange As Range
    Dim critRange As Range
    
    keyValue = Application.InputBox("Enter your search term", Type:=2)
    If keyValue = "False" Then Exit Sub: Rem cancel pressed
    
    With ThisWorkbook.Sheets("SourceSheet"): Rem adjust
        Set keyCol = Range("A:A"): Rem adjust
        Set dataRange = Range(.Cells(.Rows.Count, 1).End(xlUp), .Cells(1, .Columns.Count).End(xlToLeft))
    End With
    
    Set destinationSheet = ThisWorkbook.Sheets("destinationSheet")
    Set destinationRange = destinationSheet.Range("a1")
          
    Rem set critrange
        With destinationRange.Parent.UsedRange
            Set critRange = .Parent.Cells(1, .Column + .Columns.Count + 1)
        End With
        If critRange.Column < destinationRange.Column + dataRange.Columns.Count Then
            Set critRange = critRange.Parent.Cells(1, destinationRange.Column + dataRange.Columns.Count)
        End If
        
        Rem write criteria
        Set critRange = critRange.Resize(2, 1)
        critRange.Range("A1").Value = keyCol.Range("a1").Value
        critRange.Range("A2").FormulaR1C1 = "'=" & keyValue
    Rem filter + move
        destinationRange.Resize(, dataRange.Columns.Count).EntireColumn.ClearContents
            
        dataRange.AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=critRange, CopyToRange:=destinationRange, Unique:=False
    Rem clean-up
        critRange.EntireColumn.Delete
End Sub

(Removing the "'=" from this line will find the records that start with the search term, leaving it in will find exact matches only.)

critRange.Range("A2").FormulaR1C1 = "'=" & keyValue