VBA support in Hyperlink

[sksanjeev786]

Hi Team,

I have 40 tabs and with the help of macro, I have consolidated all tabs names in 1 sheet.

So no I wanted to hyperlink each tab name available in 1 sheet with the Actual tab Sheet.

Can anyone help me with this

Regards,
Sanjeev

 

[ranman256]

Sub BuildSheetHypers()
Dim ws As Worksheet
Const kNDX = "index"

Sheets.Add
ActiveSheet.Name = kNDX
Range("A1").Value = "SheetIndex"
Range("A2").Select
For Each ws In Sheets
   If ws.Name <> kNDX Then
        ActiveCell.FormulaR1C1 = ws.Name
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=ws.Name & "!A1", TextToDisplay:=ws.Name
      
        ActiveCell.Offset(1, 0).Select 'next row
   End If
Next
Set ws = Nothing
MsgBox "done"
End Sub

 

[sksanjeev786]

Sub BuildSheetHypers()
Dim ws As Worksheet
Const kNDX = "index"

Sheets.Add
ActiveSheet.Name = kNDX
Range("A1").Value = "SheetIndex"
Range("A2").Select
For Each ws In Sheets
   If ws.Name <> kNDX Then
        ActiveCell.FormulaR1C1 = ws.Name
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=ws.Name & "!A1", TextToDisplay:=ws.Name
     
        ActiveCell.Offset(1, 0).Select 'next row
   End If
Next
Set ws = Nothing
MsgBox "done"
End Sub

Waw!!!!! simply Awesome.

Can we have a back option for each tabs like if click to sheet 40 and after that I want to jump Sheet 1(where all links are available)

 

[ranman256]

before the NEXT ROW line:
try

'choose your cell for the BACK link:
ws.range("A1").formulaR1C1 = "sheet1"
ws.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="sheet1!A1", TextToDisplay:="Sheet1"

 

[sksanjeev786]

before the NEXT ROW line:
try

'choose your cell for the BACK link:
ws.range("A1").formulaR1C1 = "sheet1"
ws.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="sheet1!A1", TextToDisplay:="Sheet1"

Hi Ranman

Not able to execute the above macro.

Regards,
Sanjeev