Sheet Navigation

I have written a macro the selects other sheets and performs some
actions.

What i would like to do is return the user back to the original sheet
they started at. The start sheet can be different and have different names.

I guess for this i will have to have some VBA code to store the current
sheet, then i run my code, and then some more code to select the stored
sheet.

Can anyone help me out here ?

First, in almost every case, selecting other sheets is unnecessary, and
leads to slower, larger, and harder to understand code.

For instance, instead of

Sheets("Sheet1").Select
Range("A1:J10").Select
Selection.Copy
Sheets("Sheet2").Select
Range("X2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("A1").Select

you can use Range objects directly:

Sheets("Sheet1").Range("A1:J10").Copy Destination:= _
Sheets("Sheet2").Range("X2")

which doesn't change the selection at all, is much faster, and there's
no possibility of confusion over which sheet ActiveSheet.Paste refers to.


OTOH, if you want to do it the way you proposed:

Dim rSelection As Range
Set rSelection = Selection
'do your code here
Application.GoTo rSelection


In article <[email protected]>,
"WayneF" <[email protected]> wrote:

>
> I have written a macro the selects other sheets and performs some
> actions.
>
> What i would like to do is return the user back to the original sheet
> they started at. The start sheet can be different and have different names.
>
> I guess for this i will have to have some VBA code to store the current
> sheet, then i run my code, and then some more code to select the stored
> sheet.
>
> Can anyone help me out here ?

Try

Dim sHomeSheetName as String

sHomeSheetName =Activesheet.Name

near the start of your macro and

Sheets(Activesheet.Name).Select

at the end.

Doug



WayneF wrote in message ...
>
> I have written a macro the selects other sheets and performs some
>actions.
>
> What i would like to do is return the user back to the original sheet
>they started at. The start sheet can be different and have different names.
>
> I guess for this i will have to have some VBA code to store the current
>sheet, then i run my code, and then some more code to select the stored
>sheet.
>
> Can anyone help me out here ?
>
>