Using Named Range as Criteria in Autofilter

[DHGranstrand]

I am trying to autofilter a report that has a variable number of lines month to month to display only certain employees data.

I have a named range ("ShowNames") that I want Autofilter to use as the filter values.

The report is emailed to me monthly so I run the macro from a second Excel workbook which also has the named range.

The macro runs fine except that it only filters on the first value in the named range.

Here is the code:

Sub AutofilterNames()

Dim a1 As Variant

a1 = Range("ShowNames").Value

' Select Report To Filter
Windows("MasterReport.xls").Activate

' Position Curser For Range Selection
Range("A1").Select

' Autoselect Range of Data & Turn Filter On

ActiveSheet.Range(Selection.End(xlToRight), Selection.End(xlDown)).Autofilter Field:=1, _
Criteria1:=a1, Operator:=xlFilterValues

End Sub

Thanks for the help!!

 

[SkipVought]

Hi,

How do you intend to use the named range?

1. In a loop?

2. As the row source to a control?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[DHGranstrand]

I want to use the named range as the criteria for the autofilter.

ActiveSheet.Range(Selection.End(xlToRight), Selection.End(xlDown)).Autofilter Field:=1, _
Criteria1:=a1, Operator:=xlFilterValues

 

[SkipVought]

You cannot use a multi-cell range in a criteria.

You can only use ONE value per criteria.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}