AutoFilter method of Range class Failed using VBA

Question

I got this part of a code that is causing a run time error 1004. I tried looking up different cases that were solved on Stack Overflow but could not fix the issue, although I guess it is pretty simple.

Thanks in advance for your help.

Sub test2()

Dim snws As Worksheet: Set snws = ActiveWorkbook.Sheets("SN crew")

With snws
.AutoFilterMode = False
.Range("A:J").AutoFilter Field:=10, Criteria1:=Cells(1, 18).Value

End With

End Sub

Answer 1

Your problem is that the code is not in the sheet "SN crew". Try this:

'this sub in a module
Public Sub setFilter(ws As Worksheet, criteria As String)
   With ws
      .AutoFilterMode = False
      .Range("A:J").AutoFilter Field:=10, Criteria1:=criteria
   End With
End Sub


'this sub in any sheet's module you want to set filter
Private Sub callSetFilter()
   Call setFilter(Me, "your criteria here")
End Sub