"Error 1004 AdvancedFilter Method of Range Class Failed"

Rfriend

New Member
Joined
May 10, 2021
Messages
8
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:

Rfriend

New Member
Joined
May 10, 2021
Messages
8
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,631
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
May 10, 2021
Messages
8
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,566
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Sheet2.Range("HistData!Criteria") is invalid, unless sheet2 is the HistData sheet.
 

Rfriend

New Member
Joined
May 10, 2021
Messages
8
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Oct 4, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
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
 

Forum statistics

Threads
1,144,670
Messages
5,725,677
Members
422,635
Latest member
crisis

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
Top