Internal workbook link checker?

[Ironman]

Hi

I have hundreds of links to other sheets in the same workbook and I'm looking for a quick way of identifying broken links without me having to click on every single one.

I've searched the forum and I'm only able to find code for checking external hyperlinks.

Hope you can help?

Thank you!

 

[Yongle]

Test this on a copy of your workbook
- place in a new Standard module and run
- new sheet added listing cells with value #REF!

Option Explicit

Sub BrokenInternal()
    Optimise True
    Dim Ws As Worksheet, Results As Worksheet, Cel As Range, Rng As Range, x As Long, F As String, A As String, N As String
    Set Results = Worksheets.Add(before:=Sheets(1))
    x = 1
    Results.Range("A1:C1") = Array("Sheet Name", "Cell", "Formula")
    For Each Ws In ThisWorkbook.Worksheets
        Set Rng = Ws.UsedRange
            For Each Cel In Rng
                If IsError(Cel) Then
                    If CVErr(Cel) = CVErr(2023) Then
                        x = x + 1
                        F = " " & Cel.Formula: A = Cel.Address(0, 0): N = Ws.Name
                        Results.Range("A" & x).Resize(, 3).Value = Array(N, A, F)
                    End If
                End If
            Next Cel
    Next Ws
    Results.Range("A:C").EntireColumn.AutoFit
    Optimise False
End Sub

Private Sub Optimise(Tru As Boolean)
With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = Not Tru
    If Tru = False Then .Calculation = xlCalculationAutomatic
End With
End Sub

 

[Ironman]

My apologies for the delay - many thanks for your help!