Macro to return to previous worksheet

[gurs]

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.

 

[Bernie Deitrick]

gurs,

Try, with your first attempt:

Range("PrevSheet").Parent.Activate

instead of

Range("PrevSheet").Select

HTH,
Bernie

 

[steve]

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

 

[Tom Ogilvy]

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

 

[steve]

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

 

[Mike Waldron]

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

 

[Chilidog1000]

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!