Return to sheet
 

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?

Return to sheet
 

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

"Ed Davis" wrote in message
...
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?

Return to sheet
 

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 wrote:

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?

--

Dave Peterson

Return to sheet
 

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" wrote in message
...
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 wrote:

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?

--

Dave Peterson

Return to sheet
 

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

Ed Davis wrote:

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" wrote in message
...
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 wrote:

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?

--

Dave Peterson

--

Dave Peterson

Return to sheet
 

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" wrote in message
...
I wouldn't be surprised if your code could be rewritten so that no .selects
or
.activates would be included.

Ed Davis wrote:

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" wrote in message
...
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 wrote:

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?

--

Dave Peterson

--

Dave Peterson

Return to sheet
 

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.



Ed Davis wrote:

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" wrote in message
...
I wouldn't be surprised if your code could be rewritten so that no .selects
or
.activates would be included.

Ed Davis wrote:

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" wrote in message
...
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 wrote:

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?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Return to sheet
 

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.




"Dave Peterson" wrote in message
...
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.



Ed Davis wrote:

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" wrote in message
...
I wouldn't be surprised if your code could be rewritten so that no
.selects
or
.activates would be included.

Ed Davis wrote:

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" wrote in message
...
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 wrote:

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?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson