Create hyperlink to another sheet

Question

The first sheet of my workbook is like a contents page  Each cell in column A holds an IP address string  For each IP address string  there is a worksheet named with the IP address   I want to turn the string in the cells in column A into hyperlinks to their corresponding sheets  cell A1 of the destination sheet    I only need the VBA line that makes the hyperlink  I can figure out the looping  etc  Remember that the name of the sheet to be linked to is the same as the value of the cell that will become the link   Research has only brought up forum posts that give a stack of code without explaining any of it

User · Answer

The     sign is the key element  If you have a cell object  like  mycell  in following code sample  and link a cell to this object you must pay attention to   element   You must do something like this    Cells i  2  Hyperlinks Add Anchor   Range Cells i  2  Address   Address             SubAddress        amp  ws Name  amp       amp              amp  mycell Address

User · Answer

This is the code I use for creating an index sheet  Sub CreateIndexSheet       Dim wSheet As Worksheet     ActiveWorkbook Sheets Add Before  Worksheets 1   Name    quot Contents quot   Call whatever you like     Range  quot A1 quot   Select     Application ScreenUpdating   False  Prevents seeing all the flashing as it updates the sheet     For Each wSheet In Worksheets         ActiveSheet Hyperlinks Add Anchor  Selection  Address   quot  quot   SubAddress   quot   quot   amp  wSheet Name  amp   quot   quot   amp   quot  A1 quot   TextToDisplay  wSheet Name         ActiveCell Offset 1  0  Select  Moves down a row     Next     Range  quot A1 quot   EntireColumn AutoFit     Range  quot A1 quot   EntireRow Delete  Remove content sheet from content list     Application ScreenUpdating   True End Sub

User · Answer

In my implementation  the cell I was referencing could have been several options   I used the following format where  ws  is the current worksheet being edited  For each ws in Activeworkbook Worksheets     For i           For j        ws Cells i  j  Value     HYPERLINK        amp  SHEET-REF-VAR  amp        amp  CELL-REF-VAR  amp           amp  SHEET-REF-VAR  amp

User · Answer

If you need to hyperlink Sheet1 to all or corresponding sheets  then use simple vba code  If you wish to create a radio button  then assign this macro to that button ex  Home Page    Here is it   Sub HomePage       HomePage Macro       This is common code to go to sheet 1 if do not change name for Sheet1      Sheets  Sheet1   Select   OR     You can write you sheet name here in case if its name changes      Sheets  Monthly Reports Home   Select     Range  A1   Select  End Sub

User · Answer

This macro adds a hyperlink to the worksheet with the same name  I also modify the range to be more flexible  just change the first cell in the code  Works like a charm  Sub hyper    Dim cl As Range  Dim nS As String   Set MyRange   Sheets  Sheet1   Range  B16    Set MyRange   Range MyRange  MyRange End xlDown     For Each cl In MyRange   nS   cl Value   cl Hyperlinks Add Anchor  cl  Address      SubAddress       amp  nS  amp       amp    B16   TextToDisplay  nS  Next End Sub

User · Answer

Something like the following will loop through column A in the Control sheet and turn the values in the cells into Hyperlinks  Not something I ve had to do before so please excuse bugs   Sub CreateHyperlinks    Dim mySheet As String Dim myRange As Excel Range Dim cell As Excel Range Set myRange   Excel ThisWorkbook Sheets  Control   Range  A1 A5     lt  lt adjust range to suit  For Each cell In myRange     Excel ThisWorkbook Sheets  Control   Hyperlinks Add Anchor  cell  Address      SubAddress  cell Value  amp    A1    lt  lt from recorded macro Next cell  End Sub

User · Answer

I recorded a macro making a hiperlink  This resulted   ActiveCell FormulaR1C1     HYPERLINK    Workbook xlsx Sheet1 A1     CLICK HERE

[excel] Create hyperlink to another sheet

Examples related to excel

Examples related to vba

Examples related to hyperlink