AutoFilter Criteria using named range

[Len]

Hi,

I tried to use named range as AutoFilter criteria but it fails to work
using codes below
In fact I need to filter column B in sheet1 under workbook A based on
range("A1:A10") in sheet5 of workbook B

Codes extract

With Workbooks("B.xls").Sheets(5)
..Range("A1", Range("A65536").End(xlUp)).Name = "MyRange"
End With
Workbooks("A.xls").activate
Sheets(1).Select
Selection.AutoFilter Field:=2, Criteria1:=MyRange,
Operator:=xlFilterValues

Any helps will be much appreciated and thanks in advance


Regards
Len

 

[Javed]

Autofilter does not take range as criteria.I have used Advanced
Filter.Hope this will solve

With Workbooks("B.xls").Sheets(5)
Range(.Range("A1"), .Range("A65536").End(xlUp)).Name = "MyRange"
End With
Workbooks("A.xls").Activate
Sheets(1).Select


If Selection Is Nothing Then
MsgBox "select ...."
Exit Sub
End If

Selection.AdvancedFilter Action:=xlFilterInPlace,
criteriarange:=Workbooks("B.xls").Worksheets(5).Range("MyRange")

 

[Len]

Javed,

Thanks for prompt reply and your codes.
After working around with your codes in Excel 2007, there is no
response to advance filtering and prompt the message as there is no
selection made

Please help

Thanks & Regards
Len

 

[Javed]

Dear Len,

You must select the required data before running.(I have given error
trap so that if no selection this macro exits)

Advanced Filter requires one Criteria Range.Suppose your data contains
names of Customer with columnheading "Name"
Then the value of first cell in MyRange should be Name after that any
thing you want to filter may be existing.

Myrange-Cell 1 -->Name (The column heading should be exactly as per
the original column of data)
MyRange Cell2,3,4.....-->>Cutomer Name you want to filter

If it is a bit complex you may just send the two waorkbook filling
necessary data in MyRange.I will fulfill remaining.

 

[Len]

Dear Javed,

Thanks for your advice
Finally, It works perfectly using named range in advance filtering

It is bit confuse that since we can use array in criteria selection
for Auto filtering and why array can not be replaced by named range
for auto filtering


Cheers
Len