Search : in
By :

Stay on same row when selecting next sheet

Last answer on Nov 23, 2009 8:43:43 pm GMT Trowa, on Oct 20, 2009 1:49:22 pm BST 
 Report this message to moderators

Hello,

I would like to know how to stay on the same row when selecting another sheet.

My file contains 17 sheets. Sheets 2 and 5 till 16 contain names and will be refered to as target sheets.
When I select one of these names on one of the target sheets and select another target sheet, I would like the same name on the same row selected.

Example:
I input some data on row 17 of sheet 2. On sheet 5 I input some more data on the same row. So I would like row 17 to be selected when I select sheet 5.
When I input some data on row 187 of sheet 2 and go back to sheet 5, the active row will still be 17. It would speed up the process if row 187 would already be selected.

I hope I made myself clear.

Best regards,
Trowa

Configuration: Windows 2003 Internet Explorer 7.0

Best answers for « Stay on same row when selecting next sheet » in :
How to freeze a row in an excel sheet ShowHow to freeze a row in an excel sheet To freeze a line in an excel sheet, for example line 1: Select line 2. Go to menu Window and select Freeze panes.
How to read a file line by line ShowHow to read a file line by line Intro Tips Bonus Intro One of the most common errors of learning scripts bash on GNU / Linux is to read a file line by line, is to use a loop "for" (for line in $ (cat file.txt) do. ..), which in this...
How to insert picture background on Microsoft! ShowHow to insert picture background on Microsoft! To make a work sheet more appealing, you can add picture to your background. Below is a tip for doing so. Open your Microsoft and select Format> Backgrounds > Printed Watermarks in the menu...
Download SSH Secure Shell ShowSSH secure shell for workstations is a flexible client SSH allowing to connect in a secured way to remote applications. http://www.commentcamarche.net/faq/images/NHc6wz5jOYBhPXTis.png
Download Vista Live Shell Pack - Pink ShowIf you want to have Vista on your computer but your shape is not rather powerful or if simply you cannot have to be paid this version yet? Then resolution is to change the appearance of your good old XP. Vista Live Shell Pack is a topic of office...
Download CBN Selector 2.26.1.26 Show
Linux - The shell ShowIntroduction to the shell The command interpreter is the interface between the user and the operating system, hence the name "shell". The shell therefore acts as an intermediary between the operating system and the user thanks to command lines...
UNIX system - The shell ShowIntroduction to the shell The command interpreter is the interface between the user and the operating system, hence its name "shell". The shell therefore acts as an intermediary between the operating system and the user using command lines...
CSS - Style sheets ShowIntroduction to style sheets The concept of style sheets first appeared in 1996 when the W3C published a new recommendation entitled "Cascading Style Sheets", or CSS for short. The principle behind style sheets involves using a single document to...
Ask your question Reply

1

Trowa, on Oct 22, 2009 1:42:49 pm BST

I have got something like this,

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

r = ActiveCell.Row
Cells(r, 3).Select

End Sub

but the code won't recognise the active row.
How do I exclude certain sheets?
What to do?

Regards,
Trowa

   
Reply to Trowa

2

 aquarelle, on Nov 23, 2009 8:43:43 pm GMT

Hello Trowa,

I don't know if you found a solution to this question but if it is not the case, try this proposition. You have to place it in "ThisWorkbook". Normally works if you select a cell, a range of cells, a row or any sort of selections. 

Option Explicit

Dim LastTarget As String
Dim FirstVisibleCell As String

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
LastTarget = Target.Address
FirstVisibleCell = "R" & ActiveWindow.VisibleRange.Row & "C" & ActiveWindow.VisibleRange.Column
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next
Application.EnableEvents = False
Application.Goto Reference:=FirstVisibleCell, Scroll:=True
Range(LastTarget).Select
Application.EnableEvents = True
End Sub


Best regards

"Pour trouver une solution à ses problèmes, il faut s'en donner la peine."

   
Reply to aquarelle
Ask your question Reply
They need your help