Thanks for your reply. I started out trying to use a named range, but
couldn't get it to work, with or without quotes. Then I tried using it
as a variable, which didn't work either. I'm open to suggestions as to
which is best. One of the reasons I gave up on the named range was that
I thought a named range was sheet specific, but found out when I
selected FilterRange from the pull down list in the upper left corner
of the spreadsheet, it selected the range from a different sheet than
the one I was working on.

I have a bunch of sheets which all have tables I need to filter on. So
what I want it to do is get the sheet name from the original sheet
called SourceSht, dimmed as object, get the data and the filter
criteria from the sheet I call RecSht, and extract the data onto a
sheet called CtyExtr. Both the Source sheet and the records sheet will
vary, but I'll always be calling the same records sheet from the same
source sheet. I.e., "A" and "A Records", "B" and "B Records", etc. The
extracted data always goes to one named "County Extract" which has a
variable name of CtyExtr.
If this makes any sense, do you have any ideas how to make this all
happen?
TIA

Toppers wrote:
Hi,
If you use a named range, the name must be in quotes e.g.:

Dim rng as Range
Set rng=Range("NamedRange")

but as you define a FilterRange using Set (this is not the named range)
then:

FilterRange.AdvancedFilter Action:= _ ......etc should work

To use a named range:

RecSht.Range("FilterRange").AdvancedFilter Action:= _

If "FilterRange" is defined at workbook level, you can omit "RecSht." from
the above.

You are mixing Named Ranges and ranges defindd using SET so I am not sure
which method you want to use.

HTH

"davegb" wrote:

I'm trying to apply an advanced filter to a named range called
"FilterRange" on the source worksheet (RecSht).

RecSht.Select
Range("a1").Select
'Selection.CurrentRegion.Select
Set FilterRange = Selection.CurrentRegion

CtyExtr.Select
RecSht.Range(FilterRange).AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=RecSht.Range("aa1:aa2"), _
CopyToRange:=Range("A5"), Unique:=False

I tried using a named range, but it seems that macros don't recognize a
named range, at least I can't get it to. So I created a variable to
represent the current region from cell A1, called FilterRange. The
macro doesn't like that either! No matter what different variations of
syntax I use, the macro won't accept it. Right now I'm getting the
"Application defined or object defined error". FilterRange is dimmed as
an object. Any ideas?
Thanks for the help!