in the code you are looping 2 to 12, not sure why, but it errors when you try filtering a column that is beyond the columns being filtered. so i = 6 raises the error as column F is not part of the autofilter.

    For Each sh In ActiveWorkbook.Sheets
        If sh.Name <> mainsh.Name Then
            For i = 2 To 12
                With sh.UsedRange
                    .AutoFilter i, "*" & search_val & "*"
                    .Offset(1, 1).Resize(, 11).Copy mainsh.Cells(Rows.Count, 2).End(xlUp)(2)
                    .AutoFilter
                End With
            Next i
        End If
    Next
If you are matching marchant name then you should filter column 4. If Name is in different places on different sheets then perhaps you should add code to locate it first and then filter the correct column