Excel Sheets lookup/macro [Closed]

Report
Posts
1
Registration date
Wednesday May 27, 2009
Status
Member
Last seen
May 28, 2009
-
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
-
Hello,

I have a excel spreadsheet with around 50 sheets. In each sheet, in square D3, there is a name of the person who that sheet applies to.
I would like to create a macro (I think that is what I need) that allows me to type the name of somebody into it and then the macro will take me to that sheet.

Thank you all

1 reply

Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
215
1. You can just enter your sheet name and then Insert Hyper link on them

Insert > Hyperlink > book Mark> Select your desired Sheet

2. Write hyperlink Code like you are in Sheet1 then Write in Cell A2=hyperlink("[book1.xls]sheet2!A1," Go To "&Sheet2!A1)

3. I"ve made a macro and you can run it in Sheet1 then in your Column A all the sheets names will appear ..... In column B you can use my method 2 by giving reference

Sub LISTSHEETS()

Dim ws As Worksheet
Dim x As Integer

x = 1
Sheets("Sheet1").Range("A:A").Clear

For Each ws In Worksheets

     Sheets("Sheet1").Cells(x, 1) = ws.Name
     x = x + 1
Next ws
End Sub


After Running this macro in Sheet1 Cell B1 put this formula B1=HYPERLINK("[BOOK1.XLS]"&A1&"!A1","Go to Sheet") and drag it down ....... please note "Go to Sheet" is a friendly name and you can ignore it .....


1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!