"Error 1004 AdvancedFilter Method of Range Class Failed"

Rfriend

Board Regular
Joined
May 10, 2021
Messages
73
Office Version
  1. 2010
Platform
  1. Windows
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?


Rich (BB code):
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
 
Last edited by a moderator:
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
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Try this if it works : [B8].CurrentRegion.AdvancedFilter xlFilterCopy, [HistData!Criteria], [AC8:AS8]
Your last code does not use Sheet2 but the active sheet …​
 
Upvote 0
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
 
Upvote 0
Sheet2.Range("HistData!Criteria") is invalid, unless sheet2 is the HistData sheet.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top