Hyperlink multiple worksheets based on cells

leeksleeks

Board Regular
Joined
Oct 31, 2013
Messages
96
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

1st January - Tuesday
2nd January - Wednesday
3rd January - Thursday
4th January - Friday
5th January - Saturday
6th January - Sunday
7th January - Monday
8th January - Tuesday
9th January - Wednesday
10th January - Thursday

<tbody>
</tbody>
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Easiest way IMO is to create the hyperlinks in column B with the following formula:
Code:
=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.
 
Upvote 0
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)
 
Upvote 0
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")

Code:
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
 
Last edited:
Upvote 0
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.
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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