Keeping the same cell when switching worksheets?

[SethTibo]

Hello all,

I would like my cursor to stay in the same cell when I switch worksheets. If I'm in B40 on worksheet one, and switch to worksheet two I would like the cursor to be in B40.

I'm using very basic excel functions with no scripting at the moment.

Any help is appreciated, Danke

 

[Jonmo1]

This will do the whole renge as well..just change activecell.address to selection.address. Selected cells don't even have to be contiguous. (did I spell that right?)

Dim mycell 'This goes at the top of the Module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If mycell = "" Then mycell = "$A$1"
Range(mycell).Select
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
mycell = Selection.Address
End Sub

 

[Joe Was]

That is why the Event has defined a "Target!"

 

[SethTibo]

Thank you very much Pennysaver, worked like a charm

Thanks for the warm welcome as well!

 

[Smitty]

Thanks, but I actually think you'll find the other code that's been offered to be better as they keep the cell address in memory as opposed to writing them to a sheet.

Smitty