Excel 2010 using vba AutoFilter to filter table by cell value returns '1004 Autofilter method of Range class failed'

[eldarnegrin]

Hi,
I've seen here some code samples which worked for different people, yet i cant manage to make it work.
facts:
I've checked the trust vba module in the trust center.

cell A1 contains the filter value 1 (i want to show only rows that contain 1 in column a)

table data on range("A2:D6")/table:ListObject("Table1") (I've tried both)
a b c d
1 5 9 13
2 6 10 14
3 7 11 15
4 8 12 16

the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not ActiveSheet.AutoFilterMode Then ActiveSheet.Range("A2:A6").AutoFilter [COLOR=#ff0000]'tried with ActiveSheet.AutoFilterMode=False too instead of this line[/COLOR]
    Range("A2:A6").AutoFilter Field:=1, Criteria1:=Cells(1,1) [COLOR=#ff0000]'Tried with range here too instead of Cells reference, and also simple "1" as a static criteria[/COLOR]
End Sub

Nothing seems to work.. Thanks!

 

[Andrew Poulsom]

Welcome to MrExcel.

This is what the macro recorder gave me:

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:="1"

 

[eldarnegrin]

Thanks very much
and yes i know i also recorded it and it works when i run it,
how can i make it work automatically?

Sub Macro1()
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:="=" & Cells(1, 1)
End Sub

 

[eldarnegrin]

NM, solved it Thanks!

Wrong: Criteria1:=Cells(1,1)
Right: Criteria1:="=" & Cells(1, 1)

Thanks!