Autofilter Error Class Out Of Range - 1004

[DiscoSte]

Hi Folks

I am trying to use an Input Box to filter results on a seperate non-active Sheet. (ExportData). I get the error as mentioned in the title and the debug points to line 7. .Autofilter Field:=5.....
My VBA code is mostly 'borrowed' from elsewhere, so I'm not 100% sure what I've got wrong

Option Explicit
Public Sub Filter()
    Application.ScreenUpdating = False
    Dim strName As String
    strName = InputBox("Please enter required date")
    With Sheets("ExportData").Range("$A$1:$Z$1000")
        .AutoFilter Field:=7, Criteria1:="*" & strName & "*"
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

Any assistance is greatly appreciated. Thank you.

 

[Joe4]

A few things:

1. Do not use reserved words like "Filter" for the names of procedures, variables, or functions. That could cause errors and other unexpected results.
2. You do not need the "*" around your criteria. That is when you are looking for a partial text entry max, not a date match.
3. I believe your second ".AutoFilter" line is shutting off the filter you are applying in the line above.

See if thise does what you want:

Public Sub MyFilter()
    Application.ScreenUpdating = False
    Dim strName As String
    strName = InputBox("Please enter required date")
    With Sheets("ExportData").Range("$A$1:$Z$1000")
        .AutoFilter Field:=7, Criteria1:=strName
    End With
    Application.ScreenUpdating = True
End Sub