This macro produces a hyperlink index sheet.
To get list of hyperlinks in each worksheet, the Vba code in this page follow these steps.
- Add a new sheet for Output
- Loop through each Worksheet
- Loop thru each hyperlink
- Get hyperlink address, path, cell etc.,
- Print hyperlink details to output sheet.
Here is the vba code.
Sub ListAllHyperlinks() 'Get list of all hyperlink in Workbook - officetricks Dim hLink As Hyperlink 'Hyperlink Object Dim iSh As Worksheet 'Input Worksheet Dim oSh As Worksheet 'Output Worksheet Dim oRow As Double 'Output row index 'Create a new Worksheet Set oSh = ThisWorkbook.Sheets.Add 'Loop thru Each sheet oRow = 0 For Each iSh In ThisWorkbook.Sheets 'Loop Thru each Hyperlink For Each hLink In iSh.Hyperlinks oRow = oRow + 1 oSh.Cells(oRow, 1) = iSh.Name oSh.Cells(oRow, 2) = hLink.Range.Address oSh.Cells(oRow, 3) = hLink.Name oSh.Cells(oRow, 4) = hLink.Address Next Next 'Autoalign Column Width oSh.Columns.AutoFit End Sub
List hyperlinks address in whole workbook with cell address.
This macro not only lists the hyperlinks address and name, but also in creates an index for all the hyperlinks present in the sheet, along with sheet name, cell range.
So, it will be very usefull if your Excel file has too many hyperlinks. This way it is easy to maintain all the links.