Tom’s Tutorials for Excel: List all link formulas’ locations and their source workbooks, sheets, and cells.
Tom Urtis gives us another one.
For link formulas in your workbook, you can list their sheet and cell locations, AND the name of their precedent source workbook, worksheet, and cell addresses.
Sub ListSheetLinksX()
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
Dim ws As Worksheet, ListSheet$, strFormula$, xRow&, cell As Range
xRow = 3
ListSheet = "LinksList"
On Error Resume Next
Worksheets(ListSheet).Delete
Err.Clear
Sheets.Add(after:=Sheets(Sheets.Count)).Name = ListSheet
With Range("A1")
.Value = _
"List of formulas in this workbook with links to other workbooks."
Range("A2:E2").Value = _
Array _
("Worksheet in this workbook", "Worksheet cell with link formula", _
"Workbook name in link formula", "Worksheet name in link formula", _
"Link cell address")
With Range("A1:E1")
.HorizontalAlignment = xlCenterAcrossSelection
.Interior.ColorIndex = 6
End With
.CurrentRegion.Font.Bold = True
End With
For Each ws In Worksheets
On Error Resume Next
If ws.Cells.SpecialCells(3).Count = 0 Then
If Err.Number <> 0 Then Err.Clear
Else
For Each cell In ws.Cells.SpecialCells(3)
strFormula = cell.Formula
If InStr(strFormula, "[") > 0 And InStr(strFormula, "!") > 0 Then
Cells(xRow, 1).Value = ws.Name
Cells(xRow, 2).Value = cell.Address
With WorksheetFunction
Cells(xRow, 3).Value = _
Mid(strFormula, .Find("[", strFormula) + 1, .Find("]", strFormula) _
- .Find("[", strFormula) - 1)
Cells(xRow, 4).Value = _
Mid(strFormula, .Find("]", strFormula) + 1, .Find("!", strFormula) - _
.Find("]", strFormula) - 2)
Cells(xRow, 5).Value = _
Right(strFormula, Len(strFormula) - .Search("!", strFormula))
End With
xRow = xRow + 1
End If
Next cell
End If
Next ws
Cells.Columns.AutoFit
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With
MsgBox "This sheet has been updated to show the list" & vbCrLf & _
"of formulas with links to workbooks.", , "List is completed."
End Sub
This looks extremely useful! However, I get only the headings and no data on the links tab when I run it on my desired workbook. Any ideas?
Also, it would be great to run for a single worksheet, range, or cell.
Thanks!
If you only get the heading and no links, maybe in that particular active workbook there are simply no links, as in references in formulas to cells in other Excel workbooks. Regarding the request for just one worksheet, you can accomplish that by deleting this line:
For Each ws In Worksheets
and this line
Next ws
and near where
For Each ws In Worksheets
was, type in
Set ws = Worksheets(“YourSheetName”)
And if you want just a range of cells, you can have
Dim myRange as range
Set myRange = ws.Range(“A1:X55”)
But really, you should be seeing a list of links in the produced spreadsheet. I know the code works, so it’s curious that it is not happening for you if for sure you have link formulas. Please try it again and post back with your results.
Hi Tom,
looks handy, thanks. btw, I like to show cell.Address this way: cell.Address(false, false) ~ easier to read 😉
Warm Regards,
Crystal
*
(: have an awesome day 🙂
*
I often show addresses without the dollar signs as well Crystal, but I go with .Address(0,0). Same result, 8 fewer characters as I look fro any way to even out the score against the omnipresent typing gods who have pegged me as a classic typing-challenged cybergeek.
great tool! thank you so much for sharing!!