MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Return to original sheet/activecell after looping thro' worksheets


Posted by sid on October 25, 2001 1:12 AM

i am using code below to loop thro worksheets initiated in a cell. How can I return to this sheet/activecell when looping is complete?

Dim Sht As Worksheet
Dim sCurrentSheet As String
For Each Sht In Application.Worksheets
sCurrentSheet = ActiveSheet.Name
Sht.Activate

Next Sht


thanx Sid


Posted by Damon Ostrander on October 25, 2001 7:30 AM

Posted by mseyf on October 25, 2001 7:37 AM

Sid:

you need to trap the sheet/activecell name before you start looping so you can return to them ater the looping is done. Something like:

Dim sStartCell As String
Dim sStartSheet As String
sStartCell = ActiveCell.Address
sStartSheet = ActiveSheet.Name

LOOPING CODE HERE

Worksheets(sStartSheet).Select
Range(sStartCell).Select

HTH

Mark

Posted by Damon Ostrander on October 25, 2001 7:37 AM

Hi Sid,

Oops. Added an empty message by accident. Anyway, here's the solution.

Simply save the active cell before the loop, then re-activate it after. Here's how your code would look:

Dim Sht As Worksheet
Dim sCurrentSheet As String
Dim OldActiveCell As Range

'save currently active selection
Set OldActiveCell = Selection

For Each Sht In Application.Worksheets
sCurrentSheet = ActiveSheet.Name
Sht.Activate
Next Sht

'Re-activate old active cell
OldActiveCell.Activate

Happy computing.

PS. If you don't want to activate the old active cell, but rather just want to select it:

OldActiveCell.Select

Either way, the old active worksheet will re-activate and the old active cell will be selected. i am using code below to loop thro worksheets initiated in a cell. How can I return to this sheet/activecell when looping is complete?

Posted by mseyf on October 25, 2001 8:05 AM

shorter method

and even shorter method:

Range(ActiveCell.Address).Name = "StartCell"

LOOPING CODE HERE

Application.Goto "StartCell"

Posted by sid on October 26, 2001 12:33 AM

Re: shorter method

Thanx guys,
have gone with this method, it being the shortest
many thanx sid