Hyperlink numbers in a column to point to sheets

[divyaquamara]

Hi,
I have a column having Sr. no(from 1 to 500(or more)). Also I have those many worksheets in the workbook.

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD class=xl24 id=td_post_2793594 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=64 height=34>Sr.no</TD></TR>
<TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>10</TD></TR>
A

<TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_2793594 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD></TR>
</TBODY></TABLE>
So I want that the numbers here take to that Sheet number.
I have sheets having names ( 1 2 3 4 5 6 7 8 ...).
So when I click on 1 (in the main sheet) It should take me to the sheet named 1 and similarly for each number.
I want this to be automated .
Hence I want to put this in a macro.
How can I proceed.
Thank you in advance.

 

[njimack]

something like this? Select the cells in question - the code will create a link to cell A1 of each named sheet.

Sub Link()

Dim r As Range

For Each r In Selection
    r.Hyperlinks.Add Anchor:=r, Address:="", SubAddress:="'" & r.value & "'!A1", TextToDisplay:=r.value
Next r
    
End Sub

 

[divyaquamara]

Hi,
I am sorry but did not understand how to make changes to your code.
So I recorded a macro to do this but I need to edit it to automatically take the range as say range=1 to lastrow (or something like that)
the recoreded macro is as below:

Sub hyperlink()
'
' hyperlink Macro
'
'
Range("A4").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'2'!A1"
Range("A5").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'3'!A1"
End Sub

How can I edit the above macro.
Or what changes should I make to your macro.


Thanks in advance

 

[njimack]

You could have selected the range in question and then run the code I posted. Here's a version which runs it on all cells in column A, starting from row 2...

Sub Link()  
Dim Last_Row as Long
Dim i as Long
Dim r As Range  

Last_Row = range("A" & rows.count).end(xlup).row

For i = 2 to Last_Row
With Cells(i,1)
.Hyperlinks.Add Anchor:=Cells(i,1), Address:="", SubAddress:="'" & .value & "'!A1", TextToDisplay:=.value 
End With
Next i      
End Sub

 

[divyaquamara]

Hi
It gives me an error "Invalid procedure call or argument" on the line
.Hyperlinks.Add Anchor:=Cells(i, 1), Address:="", SubAddress:="'" & .Value & "'!A1", TextToDisplay:=.Value

I dont know why. I am just copying the code in a new module and running it for my data.
Do I need to add in something?

Thanks a lot.

 

[njimack]

Is the sheet in question currently selected? You didn't specify the sheetname, so I assumed you would be running the code from the sheet with your list of numbers on.

 

[divyaquamara]

Hi,
The sheet name is summary.
But I did run the code on the same sheet which has the numbers ie Summary sheet.
(I am working on Excel 2003 if this helps.)
Thanks

 

[njimack]

Very strange. If you use sheet names like Sheet1, Sheet2 etc. then it works fine. Naming your sheets 1 and 2 does throw up that error - not sure why

 

[divyaquamara]

Okay. Thanks a lot for your help.
I am not sure but can I put in an i variable in the address that indicates the sheet numbers.
I am sure if it is possible to do that.
Do have have an idea?
Thanks a lot again.

 

[divyaquamara]

Hi
It started to work I made only the following change:

Original:
.Hyperlinks.Add Anchor:=Cells(i, 1), Address:="", SubAddress:="'" & .Value & "'!A1" , TextToDisplay:=.Value

Changed it to:
.Hyperlinks.Add Anchor:=Cells(i, 1), Address:="", SubAddress:="'" & .Value & "'!A1"

Thanks a lot.