Automated autofilter error

[Simon]

'
Sub Macro3()
'
' Macro3 Macro
' Macro written 07/12/2009 by AXUKLSLAV
'
Dim Retail
Dim Retailtest

Set Retail = Workbooks.Open("N:\mis\EXCEL\EoM\DW-Reports
\Retailfigure.csv")
Set Retailtest = Workbooks.Open("N:\mis\EXCEL\EoM\DW-Reports
\RetailfigureTEST.xls")

Retail.Activate
Range("A1:C100").Copy
Retailtest.Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("H1:I1").Copy
Range("J1:K1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Dim Distribution1 As String, DistributionPath1 As Range
Set DistributionPath1 = Retailtest.ActiveSheet.Range("J1")

Dim Distribution2 As String, DistributionPath2 As Range
Set DistributionPath2 = Retailtest.ActiveSheet.Range("K1")

Range("A1:C1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=DistributionPath1,
Operator:=xlAnd
Selection.AutoFilter Field:=2, Criteria1:=DistributionPath2,
Operator:=xlAnd

Set myRng6 = Range("A3000").End(xlUp)

If myRng6 = 0 Then
MsgBox "Retail hasn't run"
Else: 'MsgBox "Has Run"
End If

End Sub


The TEST sheet is where the raw data is pasted into. Column C is the
year and column B is the month. J1 is the Year and K1 is the month, I
want to display the latest month.

I have the error with line:
Selection.AutoFilter Field:=2, Criteria1:=DistributionPath2,
Operator:=xlAnd

"Autofilter method of range class failed".

Thanks for how I can solve this.

 

[joel]

Autofilter will fail if it doesn't find at least one match I usually us
a find with an autofilter like this


Range("A1:C1").AutoFilter

set c = columns("C").find(what:=DistributionPath1, _
lookin:=xlvalues,lookat:=xlwhole)
if not c is nothing then

Range("A1:C1").AutoFilter Field:=3, Criteria1:=DistributionPath1, _
Operator:=xlAnd

set c = columns("B").specialcells(type:=xlCellTypeVisible) _
.find(what:=DistributionPath2, lookin:=xlvalues,lookat:=xlwhole)

if not c is nothing then
Range("A1:C1").AutoFilter Field:=2, Criteria1:=DistributionPath2
_
Operator:=xlAnd
end if
end i