Using named ranges in AdvacendFilter statement

[Laurence Lombard]

The statement
Sheets("Cash Bks").Range("CashBookData").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("DieselCriteria"), _
CopyToRange:=Range("DieselOutputRange"), Unique:=False

works but it gives the error "Method 'Range' of object '_Worksheet' failed"
without the Sheets("Cash Bks") part as in

Range("CashBookData").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("DieselCriteria"), _
CopyToRange:=Range("DieselOutputRange"), Unique:=False

The named range "CashbookData" refers to a range in another worksheet. I
would like to exclude the Sheets("Cash Bks") part from the VBA statement as
I want to make this sheet more adaptable ie not have to change the VBA code
if the workbook that CashBookData resides in changes. Is there a way to work
around this?

Thanks in advance
Laurence Lombard

 

[Debra Dalgleish]

If it's a workbook level name, you could do the following:

Dim rngList As Range
Set rngList = ActiveWorkbook.Names("CashBookData").RefersToRange

rngList.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("DieselCriteria"), _
CopyToRange:=Range("DieselOutputRange"), Unique:=False