gurs,

Try, with your first attempt:

Range("PrevSheet").Parent.Activate

instead of

Range("PrevSheet").Select

HTH,
Bernie


"gurs" wrote in message
...
I am having a problem writing two related macros that will switch
back
to a most recently used worksheet. Here is a description of my
structure.

I have a workbook with numerous worksheets. One of those worksheets
is
named "Criteria" and holds the criteria fields for advanced
filtering
requests. Each other sheet is a 'data' sheet named by reference to
the
date of the data ("17Jan03", "24Mar03", etc.). I have a button on
each
data sheet that executes a macro to run an advanced filter based on
the
criteria on the Criteria sheet. I also have a button to switch to
the
Criteria sheet to modify the criteria. What I need is a third macro
that will allow me to switch from the Criteria sheet back to the
data
sheet from which I came.

I thought I had a solution, albeit an inartful one, but I can't get
it
to work. Here's what I did. In the macro that switches from the
data
sheet to Criteria, I defined a name in the workbook referring to a
cell
in the requesting data sheet, as follows:

ActiveWorkbook.Names.Add Name:="PrevSheet", RefersTo:=ActiveCell,
Visible:=True

Then, in the macro that switches back to the original data sheet, I
thought I could just select the range PrevSheet as follows:

Range("PrevSheet").Select

However, I get a run-time error '1004': Select method of Range class
failed. I also tried defining the PrevSheet to refer to the
worksheet
(ActiveWorkbook.Names.Add Name:="PrevSheet",
RefersTo:=ActiveSheet.Name, Visible:=True) and then selecting the
worksheet (Worksheets(Range("PrevSheet").Value).Select), also to no
avail.

Does anyone know how I can make this structure work? Or a better
way
to accomplish my goals? Thanks.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/