"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

 

[Rfriend]

​

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.​

Hi Marc,

I ran the code in the advanced filter and it worked. I ran the marco recorder and the code changed:

Sub AdvFilter()
On Error GoTo errHandler:
With Sheet2
Range("B8:R30000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("HistData!Criteria"), CopyToRange:=Range("AC8:AS30000"), Unique:=False
End With
On Error GoTo 0
Exit Sub

This worked in solving my problem with that piece of the code.

Thank you

 

[Marc L]

​

Try this if it works : [B8].CurrentRegion.AdvancedFilter xlFilterCopy, [HistData!Criteria], [AC8:AS8]​

​

Your last code does not use Sheet2 but the active sheet …​

 

[Rfriend]

That did not work. Still getting the error. Changed to:

With Sheet2
.Range("B8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheet2.Range("HistData!Criteria"), CopyToRange:=Sheet2.Range("AC8:AS8"), _
Unique:=False

 

[RoryA]

Sheet2.Range("HistData!Criteria") is invalid, unless sheet2 is the HistData sheet.

 

[Rfriend]

Sheet 2 is " Sheet2 (HistData). Up until I posted this code I used only Sheet2. I was reading a blog that suggested using the extension with Sheet2. The code is from a training template. The original project worked fine. This is a different project where I am trying to adapt the code create a much more involved history tracking. In the original there are 10 columns in the range and copy to. My first 9 are the same format as the original. There are 8 columns after that which are all formulas in excel. The idea was to have the first 9 entered by the user on the user form, the rest of the columns search other worksheets. This would then copy all 17 to the copy to range. The search portion of the form returns all the data to the list box as it should, but the double click function of the code errors before it fills the add/edit boxes on the lower part of the user form.

 

[Huyen Ngo]

hi @Rfriend
i'am trying to run a VBA. I have confirmed the advanced filter method of range class failed . The exact same code runs fine in a different user form. Any ideas for a newbie vba user?

ThisWorkbook.Sheets("Reference data").Range("C1:I13639").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=ThisWorkbook.Sheets("Data"). _
Range("H1:H2"), CopyToRange:=ThisWorkbook.Sheets("Data").Range("A5:E5"), Unique:=False