Chart links for copied sheets
 

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

Chart links for copied sheets
 

Group the sheets and copy then all at once.

--
Regards,
Tom Ogilvy

"andym" wrote in message
...
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

Chart links for copied sheets
 

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" wrote in message
...
Group the sheets and copy then all at once.

--
Regards,
Tom Ogilvy

"andym" wrote in message
...
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

Chart links for copied sheets
 

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








"andym" wrote in message
...
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" wrote in message
...
Group the sheets and copy then all at once.

--
Regards,
Tom Ogilvy

"andym" wrote in message
...
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

Chart links for copied sheets
 

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

--
Regards,
Tom Ogilvy



"Tom Ogilvy" wrote in message
...
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








"andym" wrote in message
...
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" wrote in message
...
Group the sheets and copy then all at once.

--
Regards,
Tom Ogilvy

"andym" wrote in message
...
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

Chart links for copied sheets
 

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.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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








"andym" wrote in message
...
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" wrote in message
...
Group the sheets and copy then all at once.

--
Regards,
Tom Ogilvy

"andym" wrote in message
...
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

Chart links for copied sheets
 

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

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.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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








"andym" wrote in message
...
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" wrote in message
...
Group the sheets and copy then all at once.

--
Regards,
Tom Ogilvy

"andym" wrote in message
...
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

Chart links for copied sheets
 

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

--
Regards,
Tom Ogilvy

"andym" wrote in message
...
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

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.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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








"andym" wrote in message
...
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" wrote in message
...
Group the sheets and copy then all at once.

--
Regards,
Tom Ogilvy

"andym" wrote in message
...
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