Is it possible to create a dynamic hyperlink or Macro

[SteveC99]

I am trying to create a dynamic hyperlink or Macro which looks at a cell reference in a cell and goes to that cell in a different tab in the same file.

EG
I have a Tab called "TB" with numerous GL Codes in col A. In Column F I have created the cell address. There is a tab for each code. I have looked up the tab to show the cell address from the "TB" Tab, so that it is a different ref on each tab.

I want to be able to set up either hyperlink or macro which is dynamic enough to go to appropriate cell on "TB" tab by clicking on own tab, saving users from having to find tab

Thanks

 

[dchaney]

Try this... this assumes that the Tab Names/GL Codes are in Column A and start on Row 2... these are what will be made into your Hyperlinks

Sub Test()

Dim i As Integer, lastRow as Long

lastRow = Sheets("TB").Range("A" & Rows.Count).End(xlUp).Row

i = 2

Do Until i > lastRow

    Sheets("TB").Hyperlinks.Add anchor:=Cells(i, 1), Address:="", SubAddress:="'" & Cells(i, 1) & "'!A1"
    i = i + 1
    
Loop

End Sub

 

[SteveC99]

Thanks. That's worked. I am also trying to Hyperlink opposite way so there is a link on individual sheet to main sheet. Any thoughts?

 

[dchaney]

Here ya go, this will place a link in cell A1 on each sheet to go back to the Main Tab

Sub HyperToMain()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets

    ws.Hyperlinks.Add Anchor:=ws.Range("A1"), Address:="", SubAddress:="'TB'!A1", TextToDisplay:="To Main Tab"

Next

End Sub

To place this link elsewhere on the Tabs change the following A1 to what ever cell you desire

Anchor:=ws.Range("[B][COLOR=#ff0000]A1[/COLOR][/B]")

 

[SteveC99]

Thanks.

How do I get this Macro to look for a specific row. EG if the sheet I am on is called "140005", and there is a cell with this value on the sheet, how do I get the macro to look for this value rather than A1?

Cheers
Steve

 

[dchaney]

I am not sure why you would want the name of the current sheet to be the link to the main sheet, but try this

Sub HyperToMain()

Dim ws As Worksheet


On Error Resume Next


For Each ws In ActiveWorkbook.Sheets


    ws.Hyperlinks.Add Anchor:=ws.UsedRange.Find(ws.Name, LookIn:=xlValues), Address:="", SubAddress:="'TB'!A1"


Next


End Sub

This will look through and the first time it sees the sheet name within the sheet it will make it the hyperlink back to the TB sheet (looks through each cell of each row within the usedrange of the sheet)