Advanced Filter Macro - Won't run the filter

Hi -

I am using the below code to fun an advanced filter and copy the data to a new destination - when I do this manually the advanced filter works - however when running the macro i get an error message - "Run-time error '1004' the extract range has a mssing or illegal field name." Can someone please help me fix this error.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And Target.Column = 3 Or _
Target.Row = 2 And Target.Column = 3 Or _
Target.Row = 3 And Target.Column = 3 Or _
Target.Row = 4 And Target.Column = 3 Or _
Target.Row = 5 And Target.Column = 3 Or _
Target.Row = 6 And Target.Column = 3 Then
    Sheets("Sheet2").Select
     Sheets("Active Data").Range("A:K") _
    .AdvancedFilter Action:=xlFilterCopy, _
      CriteriaRange:=Sheets("Active Data").Range("M1:R2"), _
      CopyToRange:=Range("A1:K1"), Unique:=False

End If

End Sub

Thanks,

Hi,

Can you please upload the xls file So that we can look into what exactly is happening ?

Regards,
Naras

I would prefer not to attach the file - is there any other way to help on this?

Hi,

Apologies. I couldn't help you.

Alternative Suggestion : You can change the column names. Leave only small amount of data which is not the real one. This will maintain the confidentiality.

Naras

Thanks Naras - I created the same type of file and I am still having the same issue - I have attached via zip. Thanks for your help.