Chart links for copied sheets

[andym]

Me Again!!

having successfully been able to copy 5 sheets of an original workbook into
a new workbook, I am having problems where all my links on charts etc are
linking back to the original workbook.

Is there any way that I can set the data series of the charts to stay with
the accompanying data sheet, not the original workbook?

Of the 5 sheets I have copied over into their own workbook, 1 is the data,
the other the charts themselves.

Again, your help would be appreciated...

Regards,

andy m

 

[Tom Ogilvy]

Group the sheets and copy then all at once.

 

[andym]

Tom,

I have done just that, but still have the same problem.

Here's an example of what I have done...

Original workbook has 12 sheets.
The last 5 sheets of these 12 are the sheets I have grouped and copied into
another workbook.

In the new book, Sheet1 contains the charts, Sheet4 contains the data.
(Sheet8 and Sheet11 in the original workbook).
In the original workbook Sheet8 feeds off Sheet11. Once copied, the new
Sheet1 feeds back off the old Sheet11, not the new Sheet4.

Hope that is not confusing

I used the following code to copy the required sheets...

Sheets(Array("Sheet8","Sheet9",..."Sheet12")).copy

This copied as expected, but as mentioned, left the data series still
feeding off the orginal Sheet11.

Regards,

andy m

 

[Tom Ogilvy]

for normal linking formulas like =Sheet1!A1:A10,
Rule1: If sheets with references and all the sheets they refer to are copied
in one action, references are not set back to the original workbook.

If you get results different from this, see Rule 1.

--
Regards,
Tom Ogilvy

 

[Tom Ogilvy]

OK, I retract (mercy, mercy <g>) - this is true for regular links, but not
chartsobjects linking to other sheets. Stand by.

 

[Tom Ogilvy]

Sub Tester1()
Dim bk as Workbook, varr as Variant
Dim i as Long
Worksheets(Array("Sheet1", "Sheet2")).Copy
Set bk = ActiveWorkbook
varr = bk.LinkSources(xlExcelLinks)
For i = LBound(varr) To UBound(varr)
bk.ChangeLink varr(i), bk.FullName, xlExcelLinks
Next
End Sub

Should do it for you. Change the arguments to array to as many sheets as
you need, but make sure that you copy the source data with the charts.
Also, this assumes there are no other external links in the worksheets that
you copy.

this creates the new workbook, but could be easily modified to an existing
workbook with the same caution on existing links.

 

[andym]

Tom,

this worked perfectly. I embedded this code into an existing procedure that
creates an automatic name in which the new workbook gets saved.

Definiately a procedure to tuck away in the library for a future date.

Much appreciated ... I have now completed my project!!!

Regards,

andy m

 

[Tom Ogilvy]

Glad, after a nasty false start on my part, that you were able to get a
solution. (sorry about the false start).