VBA error: 1004 when autofiltering

[Tanyaann1995]

Hi,

I'm using the below code to use filter on a bunch of data in a worksheet. However, the error 1004 (Application defined or object defined error) keeps popping up after the Autofilter statement (highlighted below) when I execute this code. I can't figure out where I'm making the error here. Pls help.

Sub historical()

Dim i As Integer
Dim lRow As Long
Dim pno As String
Dim name As String
Dim Max_date As Date
Dim pfind As Range
Dim m As Long
Dim n As String

lRow = ThisWorkbook.Worksheets(2).Range("E:E").Find(what:="*", _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

name = InputBox("Input customer name to search against")

For i = 24 To lRow
If IsEmpty(Cells(i, 4).Value) = False Then
pno = Cells(i, 4).Value
With Workbooks("Latest hist prices 3.xlsx").Worksheets(1)
.Range("A2:DV25290").AutoFilter Field:=67, Criteria1:=pno
.Range("A2:DV25290").AutoFilter Field:=15, Criteria1:=name
Max_date = Application.WorksheetFunction.Max(.Columns("CA"))
Set pfind = .Range("CA:CA").Find(what:=Max_date, LookIn:=xlValues, LookAt:=xlWhole)
pfind.Offset(0, -27).Value = m
pfind.Offset(0, -28).Value = n
ThisWorkbook.Worksheets(2).Cells(i, 21).Value = m & n & "-" & Max_date
End With
End If
Next i


End Sub

 

[jasonb75]

Replacing the 2 filter lines with

    With .Range("A2:DV25290")
        .Autofilter
        .AutoFilter Field:=67, Criteria1:=pno
        .AutoFilter Field:=15, Criteria1:=name
    End With

Without seeing the sheet, best guess would be that there is an existing filter of less than 67 columns applied to part of the same range. If that is the case then clearing it first should resolve the issue.

 

[Tanyaann1995]

Replacing the 2 filter lines with

    With .Range("A2:DV25290")
        .Autofilter
        .AutoFilter Field:=67, Criteria1:=pno
        .AutoFilter Field:=15, Criteria1:=name
    End With

Without seeing the sheet, best guess would be that there is an existing filter of less than 67 columns applied to part of the same range. If that is the case then clearing it first should resolve the issue.

Hi,
Thanks, this code worked