Solved: Applying an Advanced Filter Using VBA

[Opv]

I am trying to test the following code to apply an advanced filter on an existing database of classmates. The script does nothing except select of the range of cells to be filtered. My criteria range includes "="Attending"" (without the outside quotation marks).


Sub filterClassmates()
Worksheets("MyContacts").Activate
Range("Q3", Range("Q1000").End(xlUp)).Select
Selection.AdvancedFilter action:=xlFilterCopy, CriteriaRange:=Range("Q1"), CopyToRange:=Worksheets("Test").Range("A3"), Unique:=True
Worksheets("Test").Activate
Range("A1").Select

What is preventing this filter from working properly?

[Aussiebear]

Have you tried "=" & "Attending" if this is what you are using to filter on?

[Opv]

Have you tried "=" & "Attending" if this is what you are using to filter on?

Thanks. I just tried that. No change. The script runs but nothing happens

[Aussiebear]

Hmmm.... what about trying the following

Criteria:= .Range("Q1")

[Opv]

Hmmm.... what about trying the following

Criteria:= .Range("Q1")

Thanks to your suggestion, I realized one of my problems. I did not include the field name in my criteria range. Let me experiment a bit more and see if I can get things working. Will advise.

[bjacobowski]

It sounds like you are trying to do an autofilter with the results copied to a different place.

If you want to use an advanced filter, the entire database must be selected and the criteria range must include the list header with the criteria below that (cell q1 = name of database field, cell q2 = attending).

[Opv]

I got the code working. Thanks for the help.

Opv