Hyperlink

[Boss]

I have ten sheets and a summary sheet.

In the column A of summary sheet i have all the other sheets names.

i need a code which will automatically link all the sheets as hyperlink to
the summary sheet.

Any help would be appreciatd.
thx!
Boss

 

[Jacob Skaria]

Try the below macro in summary sheet..

Sub Macro()
Dim lngRow As Long, lngLastRow As Long
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 1 To lngLastRow
ActiveSheet.Hyperlinks.Add Range("A" & lngRow), Range("A" & lngRow)
Next
End Sub

If this post helps click Yes

 

[Jacob Skaria]

Correction to the previous post

Sub Macro1()
Dim lngRow As Long, lngLastRow As Long
Dim myRange As Range
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 1 To lngLastRow
Set myRange = Range("A" & lngRow)
ActiveSheet.Hyperlinks.Add Anchor:=myRange, Address:="", SubAddress:= _
myRange.Text & "!A1", TextToDisplay:=myRange.Text
Next
End Sub

If this post helps click Yes

 

[Boss]

This does not work...

I need to click on A1 and it should open the corresponding worksheet in the
same file. thx!

Example:
A1.value = "test" then it should open the worsheet test

thx!

 

[Jacob Skaria]

Try the corrected version

Sub Macro1()
Dim lngRow As Long, lngLastRow As Long
Dim myRange As Range
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 1 To lngLastRow
Set myRange = Range("A" & lngRow)
ActiveSheet.Hyperlinks.Add Anchor:=myRange, Address:="", SubAddress:= _
myRange.Text & "!A1", TextToDisplay:=myRange.Text
Next
End Sub

If this post helps click Yes

 

[Boss]

Thanks a lot... That worked perfectly..

How can i remove the TextToDisplay, if i wish to keep it as blank...

thx!

 

[Jacob Skaria]

If text to display is set as blank as below; the full reference will be visible

TextToDisplay:=""

So if we keep a blank space ( as below) a underscore will be visible.

TextToDisplay:=" "


If this post helps click Yes

 

[Boss]

You helped me a lot... thx!

If text to display is set as blank as below; the full reference will be visible

TextToDisplay:=""

So if we keep a blank space ( as below) a underscore will be visible.

TextToDisplay:=" "


If this post helps click Yes

 

[Jacob Skaria]

Cheers!

If this post helps click Yes