Won't accept range name

[davegb]

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!

 

[Guest]

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]

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

 

[keepITcool]

Range names can be usefull.

but dont use too many.. and MAINTAIN them.
many problems with unreadable workbooks
relate to problems with names.

Download NameManager from Jan Karel Pieterse
(www.bmsltd.ie) and learn about global (workbook level)
and local names (worksheet level) names.

DONT use the same name on two levels.
be aware that when you copy a sheet with GLOBAL
names.. inside same workbook the copy will contain LOCAL names.
(thus you have a potential problem with mixing)

When you add names with VBA
often the easiest way is to set the name property of a range object
when assigning local names always enclose the sheetname in single
quotes...

Range("x1").Name = "'" & activesheet.name & "'!myrange"

if excel doesn't need the quotes it will ignore them,
but it wont add them if they are needed.






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


davegb wrote :

 

[davegb]

Thanks for the information. It sound as if, at least in this
application, the only thing I get by using a named range is more
complexity, of which I already have more than enough! Think I'll try
this again without range names.