Error 1004 AdvancedFilter Range Class failed

Question

Hello,

I'm working with an Office 365 (one year) account and I have a problem with excel, I'm no expert and I was recording a macro in order to create an advancedFilter to copy and paste filtered data from one sheet to another, however, even thought when I recorded the macro the process worked just fine, when I try to use it again I get the error 1004 AdvancedFilter range class failed, I was looking on the web and forums but I can't find a solution just yet. It'd be a relief if someone could help me.

My operating system it's windows 10 and here's the code:

Sub Macro2()

'

' Macro2 Macro

'

    Application.CutCopyMode = False

    Sheets("Hoja1").Range("B4:J97").AdvancedFilter Action:=xlFilterCopy, _

        CriteriaRange:=Range("Hoja2!Criteria"), CopyToRange:=Range("B6"), Unique _

        :=False

End Sub

Answer 1

Hi,

I defined the name 'Criteria' as shown in the image and updated the code part shown in BOLD below (removed Hoja2! from "Hoja2!Criteria")

You need to be in the sheet where you want the filtered rows when you run the macro. Also Row 6 on that sheet should NOT have any column heading which is not in the Hoja1 sheet, row 4.

Sub Macro2()

'

' Macro2 Macro

'

    Application.CutCopyMode = False

    Sheets("Hoja1").Range("B4:J97").AdvancedFilter Action:=xlFilterCopy, _

        CriteriaRange:=Range("Criteria"), CopyToRange:=Range("B6"), Unique _

        :=False

End Sub