"Error 1004 AdvancedFilter Method of Range Class Failed"

[Rfriend]

I am trying to run a User Form I just created and keep getting stuck on this error in my Advanced Filter. I have confirmed the data and range information but can't get past this. The exact same code runs fine in a different user form. Any ideas for a newbie vba user?

Sub AdvFilter()

On Error GoTo errHandler:

With Sheet2
.Range("B8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheet2.Range("K3:N4"), CopyToRange:=Sheet2.Range("U8:AK8"), Unique:=False
End With

On Error GoTo 0
Exit Sub
errHandler::
MsgBox "An Error has Occurred. Please notify the administrator"
End Sub

 

[Marc L]

​

As an advanced filter well works only with exact matching headers, no typo allowed !​

And here the destination has more columns than the source, a non sense …​

Try the advanced filter manually and once it works redo the same operations with the Macro Recorder activated in order to get the correct statements.​

 

[Rfriend]

Marc, Thank you for your reply. I am not sure what you mean though. Below is the exact same filter I have running in another workbook and it does run perfectly. The source. criteria, and copy_to_range is identical in both workbooks.

Again, very new and learning.

Sub AdvFilter()
On Error GoTo errHandler:

With Sheet2
.Range("C6").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheet2.Range("O6:R7"), CopyToRange:=Sheet2.Range("T6:AC6"), Unique:=False
End With

On Error GoTo 0
Exit Sub
errHandler::
MsgBox "An Error has Occurred. Please notify the administrator"

End Sub

 

[Marc L]

​

Something is bad somewhere : in the headers, the data, the criteria, the …​

​

And what "the administrator" says ?​

 

[Rfriend]

I am trying to run a User Form I just created and keep getting stuck on this error in my Advanced Filter. I have confirmed the data and range information but can't get past this. The exact same code runs fine in a different user form. Any ideas for a newbie vba user?


Sub AdvFilter()

On Error GoTo errHandler:

With Sheet2
.Range("B8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheet2.Range("K3:N4"), CopyToRange:=Sheet2.Range("U8:AK8"), Unique:=False
End With

On Error GoTo 0
Exit Sub
errHandler::
MsgBox "An Error has Occurred. Please notify the administrator"
End Sub

​

Something is bad somewhere : in the headers, the data, the criteria, the …​

​

And what "the administrator" says ?​

Thanks, I will keep looking.

 

[RoryA]

The point is that the error may not be in the code. If the headers in K3:N3 or U8:AK8 don't all exactly match headers in the data range, you'd get an error. What does:

Msgbox Sheet2.Range("B8").currentregion.address

return as a message?

 

[Rfriend]

Shhet2.Range ("B8") refers to the column header for "First Name. All three areas of the filter criteria are named identically. The advanced filter shown returns all the data, but not the case with the VBA filter I shared earlier. Please note I moved the criteria and changed the code to reflect the current cell positions accordingly from the earlier code posted.

Sub AdvFilter()
With Sheet2
.Range("B8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheet2.Range("V8:AA9"), CopyToRange:=Sheet2.Range("AC8:AS8"), Unique:=False
End With
On Error GoTo 0
Exit Sub

 

[RoryA]

That doesn’t answer my question, which is meant to determine if the code is referring to the correct range. The range in your image appears to include a blank header row so shouldn’t actually work.

 

[Rfriend]

I am trying to run a User Form I just created and keep getting stuck on this error in my Advanced Filter. I have confirmed the data and range information but can't get past this. The exact same code runs fine in a different user form. Any ideas for a newbie vba user?


Sub AdvFilter()

On Error GoTo errHandler:

With Sheet2
.Range("B8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheet2.Range("K3:N4"), CopyToRange:=Sheet2.Range("U8:AK8"), Unique:=False
End With

On Error GoTo 0
Exit Sub
errHandler::
MsgBox "An Error has Occurred. Please notify the administrator"
End Sub

The point is that the error may not be in the code. If the headers in K3:N3 or U8:AK8 don't all exactly match headers in the data range, you'd get an error. What does:

Msgbox Sheet2.Range("B8").currentregion.address

return as a message?

What is it you wnat me to do with this code?

Revised AdvFlter Code:

Sub AdvFilter()
On Error GoTo errHandler:
With Sheet2
.Range("B8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheet2.Range("V8:AA9"), CopyToRange:=Sheet2.Range("AC8:AS8"), Unique:=False
End With
Exit Sub

 

[RoryA]

I want you to copy and paste that one line I gave you into the immediate window in the VB Editor and press Enter. The message that appears should confirm the range that the code is trying to filter.