Creating multiple hyperlinks at once

[derbyowen]

I have a spreadsheet where each tab is a department and on each sheet, there is a list of employees and their salary histories.

On the first tab, I need a list of every employees name. When you click on a particular name, it goes to that employees name on the appropriate tab.

I know that I can do this with a hyperlink but I have 700+ employees and I don't want to do it 700 times for each hyperlink.

Thank you for any help that you can give!

Jennifer

 

[DonkeyOte]

Jennifer, welcome to the board.

I think some additional info is required before anyone will be able to provide a solution for you, namely:

-- are the ee names in the same location/range on each dept sheet ? (if so what is the range)
-- can the dept sheets be easily identified (ie is there a prefix to the sheet names or somesuch)

 

[derbyowen]

Thank you!

The names on the tabs are names of each department such as: Sales, Development Accounting, Corporate Accounting, Legal, etc.

Each name will be in column B however the amount of each employees in the department vary so there may be 10 employees on one tab and 85 employees on another.

Thank you again for any help that you can provide.

 

[DonkeyOte]

Jennifer,

So are the dept sheet names listed anywhere -- point being the VBA will need to know which sheets in the file it should be looking at ... if these sheets can't be established "on the fly" (based on a prefix) the code will need to be able to reference a list of some sort such that it knows to look for employee names on some sheets and not on others - does that make sense ?

For ex if you had 3 sheets:

Sheet1
D_Sheet2
D_Sheet3

If D_ was the department sheet prefix the VBA could establish it need only look at D_sheet2 & D_Sheet3 for names as Sheet1 does not have the department prefix (D_) and it could do this "on the fly" as it iterates the sheets and looks at the name of the sheet.

If you do not have these prefixes it will need some other form of reference point to know which sheets it should look at and which it should ignore.

Also you state names are in B but will be variable number of names. Is the first name always in the same row on each sheet ? If not is there some other means of establishing where the first name will appear ? Are there blanks interspersed with the names... ie assuming first name appears in B2 would the 2nd name be in B3 or could there be blanks... if there can be blanks is there a means by which to establish where the last name occurs ? ie is there data other than "names" below the last name ?

 

[Norie]

Why not take a look at the HYPERLINK worksheet function?

 

[DonkeyOte]

Norie, the names can be on any of x worksheets and in various positions - I also interpret the OP as saying the names as yet do not exist on a summary sheet... they are to be collated from the x sheets. IMO a VBA approach would be the less *expensive* approach in this case.

 

[btadams]

If you've already created a summary sheet with a list of all employees, here's a macro that will do the hyperlinking. It will ask you to select the first cell in your list of employees and then loop thru all worksheets and find the first match:

Sub HyperlinkEmployees()
Dim rngEmpName As Variant, cell As Range, strSearch As String
Dim shtTarget As Worksheet, ws As Worksheet
    
    On Error Resume Next
    Set rngEmpName = Application.InputBox("Select first cell in list of employees", "Hyperlink Names", , , , , , 8)
    If Err <> 0 Then Exit Sub
    If rngEmpName.Cells.Count <> 1 Then
        MsgBox "Please select a single cell!"
        Exit Sub
    End If
    Set shtTarget = ActiveSheet
    Set rngEmpName = Range(rngEmpName.Address, rngEmpName.Offset(Rows.Count - rngEmpName.Row, 0).End(xlUp))
    
    For Each cell In rngEmpName
        If cell.Value <> "" Then
            For Each ws In Worksheets
                strSearch = ""
                If ws.Name <> shtTarget.Name Then
                    ws.Activate
                    On Error Resume Next
                    strSearch = Cells.Find(What:=cell.Value, After:=Range("A1"), LookIn:=xlFormulas, _
                        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=True, SearchFormat:=False).Address
                    If Err = 0 Then GoTo FoundIt
                End If
                On Error GoTo 0
            Next ws
FoundIt:
            On Error GoTo 0
            shtTarget.Activate
            If strSearch = "" Then
                MsgBox "Could not find " & cell.Value
            Else
                shtTarget.Hyperlinks.Add Anchor:=cell, Address:="", SubAddress:="'" & ws.Name & "'!" & strSearch
            End If
        End If
    Next cell
    Set shtTarget = Nothing
    Set ws = Nothing
    Set rngEmpName = Nothing
    Set cell = Nothing
    
End Sub