Type mismatch on range name

I've been running the following code in a previous macro, and it ran
fine. the only things I've changed are:
1. Changed from specifying the worksheet by name (was "In Care
Records", now have set that to a variable "RecSht" which is declared as
an object.

2. Changed the CopytoRange to specifically name the spreadsheet I want
the filtered data extracted to which is CtyExtr, declared as an object.


3. Changed the filter range from a specifed range on the sheet (i.e.,
"A1:G1000" to a named range "FilterRange". I'm getting a type mismatch
error on the follwing:

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


Is this enough to figure out the problem? If not, I'll post the entire
code.
Thanks for the help.

Hi,
Since you declare Recsht as an object, you have to use it that way:
..., CriteriaRange:=RecSht.Range("aa1:aa2")

You can't have both

recsht.Range

and
sheets(recsht).Range


it can't be both an object and a string variable. It has to be one or the
other so both references should be the same.



If you get that cleared up, then as long at the name FilterRange exists, it
might work.

--
Regards,
Tom Ogilvy

"davegb" <[email protected]> wrote in message
news:[email protected]...
> I've been running the following code in a previous macro, and it ran
> fine. the only things I've changed are:
> 1. Changed from specifying the worksheet by name (was "In Care
> Records", now have set that to a variable "RecSht" which is declared as
> an object.
>
> 2. Changed the CopytoRange to specifically name the spreadsheet I want
> the filtered data extracted to which is CtyExtr, declared as an object.
>
>
> 3. Changed the filter range from a specifed range on the sheet (i.e.,
> "A1:G1000" to a named range "FilterRange". I'm getting a type mismatch
> error on the follwing:
>
> RecSht.Range("FilterRange").AdvancedFilter Action:= _
> xlFilterCopy, CriteriaRange:=Sheets(RecSht).Range("aa1:aa2"), _
> CopyToRange:=CtyExtr.Range("A5"), Unique:=False
>
>
> Is this enough to figure out the problem? If not, I'll post the entire
> code.
> Thanks for the help.
>