When working with several workbooks, copy and pasting, sooner or later an unwanted link to another workbook appears. Our workbook might work for a while until the source is moved or deleted and everything stops working. So here’s how to find those pesky things and remove them.
Go to Data->Edit Links:
“Break Link”: Excel will try to break the link but it doesn’t always work (depends on type of link). And there’s no reference to where the link may be…we have to find it ourselves.
- Search for “[” within Workbook. Cells linking to other places will contain brackets.
- Data validation
- Look through all cells with data validation, e.g. they might link to a named range in another book.
- To check if there’s unknown validations: In a sheet, select all (“Ctrl+A”) and press Data->Data validation. If any cell contains validation you’ll get a question to extend Data Validation.
- Named ranges
- Open Formulas->Name manager. See if any named ranges refers to external sources.
- Conditional Formatting
- Go to Home->Conditional Formatting->Manage rules…, select “This worksheet” and see if any rules include links.
Still have problems? Make a copy of the workbook and try deleting one sheet at the time to locate which sheet contains the link. Then delete column by column to see if the link is removed (“Edit links” in data tab will grey out).
If all else fails, run this:
Sub removeLinks Dim link As Variant If Not IsEmpty(Thisworkbook.LinkSources(xlExcelLinks)) Then For Each link In Thisworkbook.LinkSources(xlExcelLinks) Thisworkbook.BreakLink link, xlLinkTypeExcelLinks Next link End If end sub