Add a row above and a column to the left of your data on sheet2.
Sub x()
    Dim cell       As Range
    Dim rData       As Range

    With Sheet2
        .AutoFilterMode = False
        Set rData = .Range("A1", .Cells(.Rows.Count, "H").End(xlUp))
    End With
    
    Sheet3.UsedRange.ClearContents
    Application.ScreenUpdating = False
    
    For Each cell In Me.Range("A1", Me.Cells(Me.Rows.Count, "A").End(xlUp))
        Sheet2.AutoFilterMode = False
        rData.Columns(1).Value = cell.Text
        rData.AutoFilter Field:=2, Criteria1:="*" & cell.Text & "*"
        rData.Offset(1).Copy Destination:=Sheet3.Cells(Rows.Count, "A").End(xlUp).Offset(1)
    Next cell
    
    Sheet2.AutoFilterMode = False
    rData.Columns(1).ClearContents
    Sheet3.Columns.AutoFit
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub