Getting a list of links in a worksheet

[Graemea]

Hi,

Is it possible to get a list of all links in a particular worksheet to other sheets in the same workbook?

I have a workbook containing many worksheets and with lookups between lots of them so I would like a quick way to see the dependencies between sheets.

Thanks!

 

[Domenic]

The following code will list all links within the active worksheet in a newly created worksheet...

Option Explicit

Sub ListWorksheetLinks()

    If TypeName(ActiveSheet) <> "Worksheet" Then
        MsgBox "Please make sure that a worksheet is active, and try again!", vbExclamation
        Exit Sub
    End If
    
    On Error Resume Next
    Dim formulaRange As Range
    Set formulaRange = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
    If formulaRange Is Nothing Then
        MsgBox "No links found in the active worksheet!", vbExclamation
        Exit Sub
    End If
    On Error GoTo 0
    
    Dim linksArray() As String
    ReDim linksArray(1 To formulaRange.Cells.Count, 1 To 2)
    
    Dim linksCount As Long
    linksCount = 0
    
    Dim formulaCell As Range
    For Each formulaCell In formulaRange
        If InStr(1, formulaCell.Formula, "!") > 0 Then
            linksCount = linksCount + 1
            linksArray(linksCount, 1) = formulaCell.Address
            linksArray(linksCount, 2) = formulaCell.Formula
        End If
    Next formulaCell
    
    If linksCount = 0 Then
        MsgBox "No links found in the active worksheet!", vbExclamation
        Exit Sub
    End If
    
    Dim resultsWorksheet As Worksheet
    Set resultsWorksheet = Worksheets.Add(before:=ActiveSheet)
    
    resultsWorksheet.Range("A1:B1").Value = Array("Location", "Reference")
    
    resultsWorksheet.Range("A2").Resize(UBound(linksArray), UBound(linksArray, 2)).Value = linksArray
    
End Sub

Hope this helps!

 

[Graemea]

That's fantastic, thank you!