Go to same cell on any sheet

[shy9999]

Hi

I have sheets named mon,tues,wed etc & I when I click on any of the sheets, I would like to go to the same cell on the sheet activated as the sheet I just left.

So, if I'm on A7 on mon sheet, when i click fri sheet, a7 be the active cell on that sheet.
Or, if on thurs sheet on B42 & click on tues, again be on the previous address, B42, on the tues sheet.

I've tried with Worksheet_Activate & Deactivate & storing the previous address globally from Worksheet_SelectionChange & then going to that range, but with no success.

I also looked at Worksheet_Activate, but again I had no luck as I don't know how to store the previous activecell, or pass that info on & then select it on the new sheet.

I thought it would be quite easy (& it may well be), but it isn't easy enough for me !!

thanks for any pointers

 

[njimack]

See if this works for you.

Create a standard module with the following code:

Public Active As String

Then in each of your sheets, (Mon, Tues, etc) place the following in the sheet module:

Private Sub Worksheet_Activate()
Range(Active).Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Active = ActiveCell.Address
End Sub

EDIT: If you experience some screen flicker when selecting a new sheet, set ScreenUpdating to False at the start of the Worksheet_Activate event, and back to true at the end of the event.

 

[Jonmo1]

I think you need to use a combination of several events..

put this in the ThisWorkbook Module...

Public MyRange As String
 
Private Sub Workbook_Open()
MyRange = ActiveCell.Address
End Sub
 

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range(MyRange).Select
End Sub
 
 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
MyRange = Target.Address
End Sub

Hope this helps..

 

[shy9999]

Thank you so much for your solutions, but I forgot to mention that if necessary I need to scroll there too...

I've used
Application.Goto Range(MyRange), True
which works, but it positions the cell in the top left, which is ok, but it would be perfect if I get the cell in the same screen position as it was previously. But this is probably getting silly....

But many thanks again, it works!