Search and display based on cell value

[J.W. Aldridge]

On page one. I have various full names listed throughout the worksheet
in various places.

When a user clicks on a cell with a name in it, I want them to be
directed to sheet 2 where the same name (cell values) is found in
column A. (There will be profile info starting with their name in
column A.)

 

[Rick Rothstein]

I think the following may do what you are looking for. Right click the tab
at the bottom of Sheet1 (which is what I assumed you meant by "page one"),
select View Code from the popup menu that appears and then copy/paste the
following into the code window that appeared...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cell As Range
If Target.Count > 1 Or Target.Value = "" Then Exit Sub
Set Cell = Worksheets("Sheet2").Columns("A").Find(Target.Value, _
LookAt:=xlWhole, MatchCase:=False)
If Not Cell Is Nothing Then
Worksheets("Sheet2").Activate
Cell.Select
End If
End Sub

 

[J.W. Aldridge]

Thanx...

Works perfect on a new workbook where the sheets are "Sheet 1" and
"Sheet 2".
However, when using my existing workbook, it only gives me the
following error:
Run time error 9
Subscript out of range

Would the code suggested be specific to it being the first and second
sheet?
I tried replacing the "sheet 2" with the actual sheet names and it
didnt work for me.

 

[J.W. Aldridge]

must be something i did...

transfered over to blank sheet and it works.

will make do.


thanx again!!!!

 

[Rick Rothstein]

Since the code goes in the worksheet module where the names are scattered
all-about ("page one", Sheet1 or whatever you called it), that worksheet's
name will not appear in the code. What you referred to as Sheet2 in your
original post (the sheet that contains the list of names in Column A)
appears twice in my code... you have to replace both occurrences with your
worksheet's actual name.