Excel VBA AutoFilter on user selection run-time er

Question

I have an Excel 2010 workbook containing 2 sheets ("Contents" and "Folders").

The purpose of this workbook is to track different pieces of work by supplier or reference number, with a front-end (the Contents page) that is simple to use, consisting only of buttons and a search box (Which isn't actually a separate box, but simply the contents of cell J8 of the Contents sheet (hereafter referred to as J8) as typed by the user.

The buttons will filter by supplier type (and work perfectly fine) but it's the user selection that I'm having trouble with.

My code for this macro is:

    Sub Find_Click()
    Dim userSelect As String
    userSelect = "*" & Range("J8") & "*"
    Sheets("Folders").Select
    ActiveSheet.Range("$B$1:$B$5000").AutoFilter Field:=2, Criteria:=userSelect, Operator:=x1And
    End Sub

When the 'Find' button is pressed, this should read J8, then select the Folders sheet and filter the result to show every entry in column B that contains the text in J8.

This was working fine. However, now when I try to use this macro I get a 1004 run-time error with the 'Application-defined or object-defined error' message.

Can anyone please help?

EDIT:

The Contains buttons that have macros assigned that follow this format:

Sub Button1_Click()
Sheets("Folders").Select
ActiveSheet.Range("$A$1:$A$5000").AutoFilter Field:=1, Criteria1:= _
    "Criteria"
    Set r = Range(Range("A3"), Range("A3").End(xlDown))
j = WorksheetFunction.CountA(r.Cells.SpecialCells(xlCellTypeVisible))
'MsgBox j
If j = 0 Then
    MsgBox "There is currently no work relating to Criteria"
    ActiveSheet.AutoFilterMode = False
    ActiveSheet.Range("A3").Select
    Sheets("Contents").Select
End If
End Sub

There is also a resest button that clears a filter and returns to the Contents sheet:

Sub Reset_Click()
ActiveSheet.ShowAllData
Sheets("Contents").Select
End Sub

Answer 1

For anyone who is interested, the problem ended up being in the line:

ActiveSheet.Range("$B$1:$B$5000").AutoFilter Field:=2, Criteria1:=userSelect

As the code was filtering only column B, the Field value needed to be set to '1' instead of my original '2'

Thanks to @R3uK for his invaluable help!

Answer 2

Generally, you'll need to activate a cell inside the range in which you are going to use the AutoFilter.

Further more, when you are trying to use AutoFilter with wildcards (* or ?) or math test, you'll need to add an = at the start of your criteria string, so

userSelect = "=*" & Range("J8") & "*"

Then, it is not Criteria, but Criteria1 and Criteria2 if you use a second one! So you don't need an Operator in this case.

And finally with ActiveSheet.Range("$B$1:$B$5000").AutoFilter Field:=2, you are asking the code to filter on the second column of a range where there is only one column!

So if you want to filter on col B, just change Field:=2 to Field:=1

---

Here is the working code :

Sub Find_Click()
Dim userSelect As String
Dim wS as Worksheet
userSelect = "=*" & Range("J8") & "*"
Set wS = Sheets("Folders")
wS.Activate
wS.Range("B1").Activate
If Not wS.AutoFilterMode Then wS.AutoFilterMode = True
wS.Range("$B$1:$B$5000").AutoFilter Field:=1, Criteria1:=userSelect
End Sub

And you also had a typo in xlAnd, it was x1And ;)