PDA

View Full Version : External links (defined named) won't delete



theta
05-16-2011, 08:26 AM
Hi all...thought i'd come here as I always receive sterling advice.

I have 3 tabs (Section 1, Section 2, Section 3) that contain named ranges.

These all link to eachother e.g. Section 1 has a named range called 'Section1TrafficPeriod'. The Section 3 tab links to this as =Section1TrafficPeriod

This allows me to make changes on Section 1 that are reflected in Section 2 and Section 3 etc

The macro that I have copied Section 1, 2 and 3 into a new workbook. I then need a macro to delete the external links, as they still refer back to the parent workbook.

I have tried the following (and it does not work). Some of them are scoped as 'Workbook' and these are fine, i.e. they are relative to the active workbook. Others are scoped to 'Section 1' and these refer back to the original workbook, thus becoming #REF when it is closed...

theta
05-16-2011, 08:32 AM
Remember, the link is only present once in the original workbook, with interlinks between section 1, 2 and 3.

After moving the pages across to a new workbook, the name manage then appears as follows (with various scopes). The workbook scope named ranges are correct and fine, the others need deleting but my breaklinks macro doesn't work in this example :

http://www.upload-jpg.com/view_image/ea2091704/name.jpg



Public Sub BreakLinks()
Dim xLinks As Variant
xLinks = ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks)
Dim i
If Not IsEmpty(xLinks) Then
For i = 1 To UBound(xLinks)
ActiveWorkbook.BreakLink Name:=xLinks(i), Type:=xlLinkTypeExcelLinks
Next i
End If
End Sub

theta
05-16-2011, 09:24 AM
After the macro closes, all of those nice (workSHEET scope only) defined names (with an .xlsm) become #REF errors as the parent workbook is closed.