Return to sheet

[Ed Davis]

I have a workbook that has over 90 sheets. After invoking a macro that
changes to other sheets I would like to return to the sheet where the macro
first started from.

Does anyone know how I can do this?

 

[Paul B]

Ed, give this a try

Sub GoBackToSheet()
Dim OS As Worksheet
Dim OC As String
Set OS = ActiveSheet
OC = ActiveCell.Address

'Your code here

OS.Select
Range(OC).Activate

End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Dave Peterson]

I think I'd try to rewrite the macro so that it didn't use .select's and
..activate's. Just updated the objects directly.

But one more way:

'declare some variables
Dim mySelection as range
Dim myActCell as Range

'do this at the top.
set myActcell = activecell
set mySelection = selection

'do lots of stuff

'right before you finish
application.goto mySelection
myActcell.activate

 

[Ed Davis]

The reason I use .select and .activate is that my macros open another file
and then I copy values from several areas from several sheets and import the
data to the other open file. So I am bouncing in and out of both workbooks.

 

[Dave Peterson]

I wouldn't be surprised if your code could be rewritten so that no .selects or
..activates would be included.

 

[Ed Davis]

It wouldn't surprise me either but I have spent hundreds of hours in past
couple of years on these workbooks that I would not know where to start. I
was very good with macros and formulas in Lotus and it was H--- trying to
convert them to excel.

I have hundreds of lines in macros in these sheets.

 

[Dave Peterson]

One of the nice things about cleaning up the code by removing the .selects and
..activates is that the code usually gets lots smaller.

And easier to update later.

It wouldn't surprise me either but I have spent hundreds of hours in past
couple of years on these workbooks that I would not know where to start. I
was very good with macros and formulas in Lotus and it was H--- trying to
convert them to excel.

I have hundreds of lines in macros in these sheets.

 

[Ed Davis]

I am sure you are right but at this point I would not know where to begin.
The major file I work with is over 13 meg with about 100 sheets. Each day
we use at least 4 sheets. However I am sure a very large part of the size is
in the programming part. Although I do have hundreds of formulas in each of
the 4 sheets we use.

Maybe someday I will try to update it. It would be a challenge for me.

I do want to thanks you for all your help I have come a long way in just a
couple of days that I have been in this workgroup.