Macro to return to previous worksheet
 

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/

Macro to return to previous worksheet
 

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/

Macro to return to previous worksheet
 

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/

Macro to return to previous worksheet
 

This combination worked for me:

Sub GotoCriteria()
ActiveWorkbook.Names.Add _
Name:="PrevSheet", _
RefersTo:=ActiveCell, _
Visible:=True
Application.Goto Worksheets("Criteria").Range("A1")
End Sub


Sub GoBack()
Dim rng As Range
On Error Resume Next
Set rng = ThisWorkbook.Names("PrevSheet").RefersToRange
On Error GoTo 0
If Not rng Is Nothing Then
Application.Goto Reference:=rng, _
Scroll:=True
End If
End Sub


--
Regards,
Tom Ogilvy


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

Macro to return to previous worksheet
 

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/

Macro to return to previous worksheet
 

Try this:
dim sheetname above all of your code
keep the private subs in the same module as your macro
use call GotoCriteria and
call ReturnPervious inside of your macro
inorder to switch back forth

dim sheetname as string
sub yourmacro()

your code here when you want to switch to cirteria

call GotoCriteria

end sub

private sub GotoCriteria()
sheetname = activesheet.name
sheets(criteria).activate
end sub

private sub RetunPrevious()
sheets(sheetname).activate
end sub

Regards,
Mike
-----Original 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/

.

Macro to return to previous worksheet
 

If your macros are sitting on the individual sheets, I
have inserted a module into the workbook and had each
sheet call the module that then directs it to the next
sheet. Kind of a switchboard where the Private sub calls
the Macro in the Module using the follwing expression:

Application.Run "'Headcount.xls'!Tester2"

where Headcount.xls is the workbook name and Tester2 is
the macro. In Tester2 is the command to call the next page
and so forth. Hope that helps!


-----Original 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/

.