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.
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
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.
looks handy, thanks. btw, I like to show cell.Address this way: cell.Address(false, false) ~ easier to read 😉
(: 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!!