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...
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
<!-- / message -->
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...
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
Code:
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
