Hyperlink multiple worksheets based on cells

[leeksleeks]

Hi,

I have a worksheet called 'Dates' and listed in column A is the dates below. I also have an individual worksheet in the same file for each of these dates so I was wondering if there is a quick way to hyperlink each of these cells to the worksheet that has the same name? I have 1st Jan to 31st of Dec so doing them all individually will take a lot of time? Any help with this would be greatly appreciated

[TABLE="width: 194"]
<tbody>[TR]
[TD]1st January - Tuesday[/TD]
[/TR]
[TR]
[TD]2nd January - Wednesday[/TD]
[/TR]
[TR]
[TD]3rd January - Thursday[/TD]
[/TR]
[TR]
[TD]4th January - Friday[/TD]
[/TR]
[TR]
[TD]5th January - Saturday[/TD]
[/TR]
[TR]
[TD]6th January - Sunday[/TD]
[/TR]
[TR]
[TD]7th January - Monday[/TD]
[/TR]
[TR]
[TD]8th January - Tuesday[/TD]
[/TR]
[TR]
[TD]9th January - Wednesday[/TD]
[/TR]
[TR]
[TD]10th January - Thursday[/TD]
[/TR]
</tbody>[/TABLE]

 

[bobsan42]

Easiest way IMO is to create the hyperlinks in column B with the following formula:

=HYPERLINK("#"&"'" & A1 & "'!A1",A1)

This will always take you to cell A1 on the required sheet.


The other (more complicated) way to make it work is:
make an actual hyperlink, Fill down in the column, copy the date values only over the hyperlinks, then use VBA and create the necessary code under Worksheet_FollowHyperlink event.

 

[Yongle]

@bobsan42 - nice solution BUT
=HYPERLINK("#"&"'" & A1 & "'!A1",$A$1)

 

[bobsan42]

Sorry, Yongle - you are wrong.
The change you propose will always take the Hyperlink text from cell A1.
The only possible change is :
=HYPERLINK("#"&"'" & A1 & "'!A1",$A1)

the reference to cell A1 on the other sheet is in the quotes: =HYPERLINK("#"&"'" & A1 & "'!A1",$A1)

 

[Yongle]

@bobsan42 oops!

 

[leeksleeks]

Thank you so much bobsan42. This works perfectly

 

[My Aswer Is This]

Here is a script I wrote.

In the script modify the sheet named Master to your needs.

This script will do this:

1. It will clear all current data in Sheet Named Master Column(A)
2. It will now enter all your sheet names into Column(A) of sheet named Master
3. It will enter a link to all those Sheet names shown in Column(A) of sheet named Master.
4. This link will take you to Range("A1") of that sheet.
5. In Range("A1") of each Sheet will be a link that will take you back to Sheet named Master Range("A1")

Sub AddHyperLinks()
'Modified  2/1/2019  8:23:20 AM  EST
Dim C As Range
Dim i As Long
Dim ans As String
ans = "[COLOR=#ff0000]Master[/COLOR]" 'Modify this name if needed all sheet names will be put in Column "A" of this sheet
Sheets(ans).Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Clear
    For i = 2 To Sheets.Count
    Sheets(ans).Cells(i, 1).Value = Sheets(i).Name
    Sheets(i).Cells(1, 1).Value = Sheets(ans).Name
    Sheets(i).Cells(1, 1).Hyperlinks.Add Anchor:=Sheets(i).Cells(1, 1), Address:="", SubAddress:="'" & Sheets(i).Cells(1, 1).Value & "'!A1"
    Next

With Sheets(ans)
    For Each C In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
        .Hyperlinks.Add Anchor:=C, Address:="", SubAddress:="'" & C.Value & "'!A1"
    Next C
End With
End Sub

 

[My Aswer Is This]

My previous script assumes your Master sheet is always Sheet(1) in your Workbook.

If that is not the case this script will move your Master sheet to be in Sheet(1) Position and then run script.

Sub AddHyperLinks()
'Modified  2/1/2019  9:13:34 AM  EST
Dim C As Range
Dim i As Long
Dim ans As String
ans = "Master" 'Modify this name if needed all sheet names will be put in Column "A" of this sheet
Sheets(ans).Move Before:=Sheets(1)
Sheets(ans).Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Clear
    For i = 2 To Sheets.Count
    Sheets(ans).Cells(i, 1).Value = Sheets(i).Name
    Sheets(i).Cells(1, 1).Value = Sheets(ans).Name
    Sheets(i).Cells(1, 1).Hyperlinks.Add Anchor:=Sheets(i).Cells(1, 1), Address:="", SubAddress:="'" & Sheets(i).Cells(1, 1).Value & "'!A1"
    Next
With Sheets(ans)
    For Each C In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
        .Hyperlinks.Add Anchor:=C, Address:="", SubAddress:="'" & C.Value & "'!A1"
    Next C
End With
End Sub