Automatically create Hyperlinks based upon links

j3andc

Board Regular
Joined
Mar 4, 2002
Messages
172
I have a summary table of information that is linked to multiple sheets. Each row is linked to a different sheet. Is it possible to automatically create a hyperlink based upon one of the linked cells in each row.

thanks
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
This will creatwe a hyperlink from the cell to the sheet if the cell and sheet name match.
Code:
Sub ADMIN_HYPERS()
'==========================================
'DEFINE (& ASSIGN) VARIABLES
'==========================================
Dim s1 As String: s1 = "Sales Order" 'change sheet name to suit
Dim co_1 As Integer: co_1 = 2 'column containing values to assess
Dim rw_1 As Long: rw_1 = 1 'first row in range containing possible hyperlink
Dim rw_2 As Long: rw_2 = Sheets(s1).UsedRange.Rows.Count
Dim h_val As Variant
Dim e As Variant
'==========================================
'LOOP DEPTS & ADD LINKS
'==========================================
Do Until rw_1 > rw_2
    h_val = CStr(Sheets(s1).Cells(rw_1, co_1))
    On Error GoTo Handler:
    e = Sheets(h_val).Cells(1, 1)
    Select Case CStr(e)
        Case "1"
        'do nothing -- error
        Case Else
            Sheets(s1).Hyperlinks.Add Anchor:=Sheets(s1).Cells(rw_1, co_1), _
                Address:="", _
                SubAddress:="'" & h_val & "'!A1", _
                TextToDisplay:=h_val
    End Select
    e = 0
    On Error GoTo 0
rw_1 = rw_1 + 1
Loop
'==========================================
'END
'==========================================
Exit Sub
Handler:
e = 1
Resume Next
End Sub

Function GET_COL(s1 As String, crit As Variant, Rw As Long, m_ord As Integer)
GET_COL = Application.WorksheetFunction.Match(crit, Sheets(s1).Rows(Rw), m_ord)
End Function

Function GET_ROW(s1 As String, crit As Variant, co As Integer, m_ord As Integer)
GET_ROW = Application.WorksheetFunction.Match(crit, Sheets(s1).Columns(co), m_ord)
End Function
 
Upvote 0
I have a summary table of information that is linked to multiple sheets. Each row is linked to a different sheet. Is it possible to automatically create a hyperlink based upon one of the linked cells in each row.

thanks

Yes. But it's hard to help without knowing what/how you're doing it. I'd start by recording a macro, performing the tasks, then edit macro to work the way you want.
 
Upvote 0
The only think that will match is the sheet name in the formula linking back to that sheet. Is it possible to utilize the sheet name from the formula to do that in a macro?
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top