Autofilter method of class failed

[bnwash]

Hi
I have a worksheet which I use to find accounts paid
It is rather basic but does the job.
I am getting a run time error 1004 every time I press the search button
Can I ask for assistance please regarding the code
I have attached an example which highlights the problem

Assistance appreciated and suggestions of what I have done wrong here because I am keen to learn more about excel

[Andy Pope]

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

[bnwash]

Am I able to ask you to correct the code for me so it works without the error

[Andy Pope]

Assuming you only need to autofilter the name field on a sheet and that field is in the same column on all sheets, try

    For Each sh In ActiveWorkbook.Sheets
        If sh.Name <> mainsh.Name Then
                With sh.UsedRange
                    .AutoFilter 4, "*" & search_val & "*"
                    .Offset(1, 1).Resize(, 11).Copy mainsh.Cells(Rows.Count, 2).End(xlUp)(2)
                    .AutoFilter
                End With
        End If
    Next

[bnwash]

Many thanks for the assistance