How to create link to different worksheet in excel

[Novicelearner]

Hi,

I would like to know is it possible to create a dynamic link that i can select different worksheet in excel.

Example:
i) Once i have create the macro i will link it to a button on cell A20 and it will select sheet"1000 Training".
ii) I will copy the button to A21 and it will select the next sheet which is sheet"2000 Training". (this does not working as it still select sheet"502080 Training")

Below is the macro create for (i)

Sub SelectSheet()
'
' SelectSheet Macro

Range("A20").Select
Sheets("502080 Training").Activate
End Sub


Thanks!

 

[My Aswer Is This]

How about if we put a hyperlink in column A for each sheet name in your workbook and when you click on the hyperlink you would be taken to that sheet.

If that would work give me the name of the Master sheet and do we need to start putting the links in "A20" or can we start in ("A2")

Is there any thing in column A we do not want to write over?

And I could provide a link on each sheet in Range("A1") which would take you back to the Master sheet.

If this would work let me know. And provide those details I asked about.

Copying buttons and putting these buttons in each cell and then adding scripts to the buttons is something I'm not able to do.

And then also if you added more sheets later just run this script again and all the new links would be added.

 

[Novicelearner]

Hi Sir,

Thank you for your response.

I have tried hyperlink but the problem with it is that everything the name of the worksheet changes, it will not work.

i need to putting the link starting from "A2" until the end.

There is nothing to be writeover in column A.

i have created a link to take me back to the master sheet.

below are some of my example.

Sub link()
'
' link Macro
'
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'100000 Training'!A1"
End Sub

Sub back()
'
' back Macro
''
Sheets("Master List").Select
Range("A1").Select
End Sub

 

[My Aswer Is This]

You can try this if you like.
This script assumes your master sheet is named "Master" change the script if you want.
try it on a empty workbook and see how it works. It will put sheet names in column A of sheet named "Master"

And a link on each sheet in Range("A1") back to the master sheet.

If you change the sheet names or add new sheets you will need to run the script again.
Even with buttons if you modified the sheet names you would have to modify the script in the button.
The only other way is to use the sheet codename. But then that would not give you a name like "Bob" or "Jane" it would give you a name like "Sheet7"

Sub AddHyperLinks()
'Modified 12-17-17 10:32 PM 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).Range("A2: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

 

[Novicelearner]

Thank you sir. will give it a try