ExcelBanter - Phantom Links

Here is a macro I wrote forever ago which should find any formulas with
external references and list them on a new sheet.

Sub FindExtRef()
'This macro should find any formulas with external references, and list them
on a new sheet.
'DOES NOT LIST EXTERNAL REFERENCES THAT ARE JUST LABELS (TEXT).
'Declare local variables.
Dim x As Long, c As Range, y As Long, z As Long
Dim NuSht As Worksheet, HitCount As Long, Msg7 As String
On Error Resume Next
'Add a new worksheet to the current workbook at the end.
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
Set NuSht = ActiveSheet
HitCount& = 1
'Check every sheet in turn.
DoEvents
For x = 1 To Worksheets.Count
'Activate each sheet in turn.
Sheets(x).Activate
'If this sheet has any formulas, then select all cells with formulas in them.
If HasRx(ActiveSheet) = True Then
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) .Select
'Check every cell in the selected range.
For Each c In Selection
'Check every character in the formula.
For y = 1 To Len(c.Formula)
'First look for a [ character.
If Mid(c.Formula, y, 1) = "[" Then
'If [ was found, check the rest of the formula for a ! character
For z = y + 1 To Len(c.Formula)
If Mid(c.Formula, z, 1) = "!" Then
'Found an external reference! Store the cell's sheet name, address, and
formula on NuSht.
HitCount& = HitCount& + 1
NuSht.Cells(HitCount&, 1).Value = "'" & ActiveSheet.Name
NuSht.Cells(HitCount&, 2).Value = "'" & c.Address
NuSht.Cells(HitCount&, 3).Value = "'" & c.Formula
Exit For
End If
Next z
End If
Next y
Next c
End If
Next x
'Done. Clean up. Add headings for the output rows and resize all columns on
NuSht.
If HitCount& = 1 Then
MsgBox "No external references were found", _
vbInformation, "FindExtRef macro"
Application.DisplayAlerts = False
NuSht.Delete
Application.DisplayAlerts = True
GoTo FER_Cleanup
End If
NuSht.Cells(1, 1).Value = "Sheet"
NuSht.Cells(1, 2).Value = "Cell"
NuSht.Cells(1, 3).Value = "Formula"
NuSht.Cells.Select
NuSht.Cells.EntireColumn.AutoFit
Calculate
NuSht.Activate
FER_Cleanup:
'Free object variables.
Set NuSht = Nothing
Set c = Nothing
MsgBox "Done!"
End Sub

Public Function HasRx(Wksht As Worksheet) As Boolean
'Function to check if the specified sheet has any cells with formulas. Using
'SpecialCells generates an error if there are NO cells with formulas.
On Error GoTo HRXerr
If Wksht.Cells.SpecialCells(xlCellTypeFormulas).Count 0 Then
HasRx = True
End If
Exit Function
HRXerr:
HasRx = False
End Function

Paste the code above in a VBA module in your workbook. Run it from any
worksheet in the workbook (Tools Macro Macros FindExtRef Run).

If you are new to macros, this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/200...e-elses-macro/

Hope this helps,

Hutch

"Engineering Accountant" wrote:

I have a large spreadsheet linked to 20-30 other files. Is there any way to
find a spreadsheet reference to a linked sheet easily?