Remove remote references in VBA without "BreakLink"

[tkstock]

Thanks, Tushar, for your response.

The problem is that the chart is referring not to the parent workshee
but to another worksheet in the same workbook. When I copy all th
sheets over at once, it still keeps its reference back to the origina
workbook.

More info on BreakLink:

BreakLink Method
See Also Applies To Example Specifics
Converts formulas linked to other Microsoft Excel sources or OL
sources to values.

expression.BreakLink(Name, Type)

expression Required. An expression that returns one of the objects i
the Applies To list.

Name Required String. The name of the link.

Type Required XlLinkType. The type of link.

XlLinkType can be one of these XlLinkType constants.
xlLinkTypeExcelLinks A link to a Microsoft Excel souce.
xlLinkTypeOLELinks A link to an OLE source.

Example
In this example, Microsoft Excel converts the first link (an Excel lin
type) in the active workbook. This example assumes at least one formul
exists in the active workbook that links to another Excel source.


Code
-------------------
Sub UseBreakLink()

Dim astrLinks As Variant

' Define variable as an Excel link type.
astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

' Break the first link in the active workbook.
ActiveWorkbook.BreakLink _
Name:=astrLinks(1), _
Type:=xlLinkTypeExcelLinks

End Su

 

[Jon Peltier]

Tom -

The problem is that the chart is referring not to the parent worksheet
but to another worksheet in the same workbook. When I copy all the
sheets over at once, it still keeps its reference back to the original
workbook.

Here's another approach. Don't copy the sheets, move them, then close the parent file without
saving changes. This way, the chart thinks the original is moving, and keeps linked to it.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______