Can I search for a worksheet by Tab Name?

J

Jerry L

I am using Excel to track data on my employees. It would be helpful if I
could search of the employee's last name, in stead of scanning the tabs.

Thanks,
Jerry
 
M

Mike H

Hi,

You could do this. Right click you sheet tab, view code and paste the code
below in.
Type an employee (Sheet name) in a1 and the sheet is selected.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Sheets(Target.Text).Activate
End If
End Sub

Mike
 
G

Gary''s Student

Say each sheet has the employee name. Make a summary sheet with the names in
column A. Might look like:

Jones
Ravenswood
Smith

If you like, put them in alphabetic order.

Then insert the following event macro in the worksheet code area:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Cancel = True
On Error Resume Next
Sheets(Target.Value).Activate
If Err <> 0 Then
MsgBox ("Sheet not found")
Err = 0
End If
End Sub

Now if you double-click on a person's name in column A, you will
automatically be taken to the correct worksheet. If there is a problem, you
will get a message.
 
Top