Found this to work in Excel97

Public ws As String

Sub namesheet()
ws = ActiveSheet.Name
Sheets(2).Select
End Sub

Sub prevsh()
Sheets(ws).Select
End Sub


--
sb
"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/