Internal workbook link checker?

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Test this on a copy of your workbook
- place in a new Standard module and run
- new sheet added listing cells with value #REF!

Code:
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
 
Last edited:
Upvote 0
My apologies for the delay - many thanks for your help!
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top