First, it's very rare to have to use any select's/activate's in your code.
Instead of:

worksheets("Sheet2").select
range("a1").select
selection.value = "hi"

You could use:
worksheets("Sheet2").range("A1").value = "hi"

If you rewrite your code, you may find that it's easier to maintain and debug.

But you could save the current location before you do anything:

Option Explicit
Sub YourSubNameHere()

Dim ActCell as range
dim CurSel as range

set cursel = selection
set actcell = activecell
...
do a whole bunch of stuff.
...
application.goto cursel
actcell.activate

End Sub


wrote:

Thanks to ALL who answered.

I may not've stated my Q clearly enough. I forebore from providing any
context in an attempt to be simple. Which was prob. a bad idea.

The user launches my Sub by macro keystroke (Shift-Ctrl-<x).

After killing ScreenUpdate, the Sub walks thru various sheets 'n'
cells in the workbook and does stuff. My Q is:

On exit, how do I return to the user's cell, so when I reenable the
screen, he has no clue I was traveling all around his WB?

I do have a working solution: On Sub launch (in Groton?), I push
ActiveCell into a var, and before exit I pop it with
OrigCell.Sheet.Activate followed by OrigCell.Activate.

But to me that smells like a kluge. I don't have to do that in a
Function, do I? Application.Caller retains its value thruout a
Function's life, doesn't it? Ergo, what's a Sub's equivalent?

It seems to me Excel oughta know the user's home cell thruout a
routine's life. This pervert, at any rate, doesn't care whether it's a
Function, Sub, or event handler (in which case you're handed Target on
a silver platter anyway, right?).

But on further thought, well, I guess I can surmise why the answer
might be no. Because Subs can do things Functions can't, right? Like
do interactive things, Goto places, etc. So yeah, I comprehend and
readily accept such differences.

So it's a yes-no question. I apologize if I caused folks to answer as
if otherwise.

And again, we are all indebted to you Knights of the Net.

***

--

Dave Peterson