I have a worksheet that contains one chart and and the chart's source data in
rows and columns in that sheet. If I select the worksheet and then move it
to another workbook, the copied chart's source data still references the
worksheet in the original workbook.

For example, in Book1!Sheet1, the chart source data is 'Sheet1'!$A$1:$B$6.
When I copy the sheet to Book1, the chart source data is
'[Book1]Sheet1'!$A!1:$B!6.

Is there a way to have the source data in the copy reference the new
workbook or do I need to programmatically adjust the source data after
copying the sheet?

The graph of course needs data to read from, so if you don't want a link to
the host workbook, you will need to copy the graph sheet AND its data sheet
together (ie simultaneously) to the new workbook.

Sheets(Array("Data", "Chart")).Select
Sheets(Array("Data", "Chart")).Copy


Martin



"Drew Lettington" wrote:

I have a worksheet that contains one chart and and the chart's source data in
rows and columns in that sheet. If I select the worksheet and then move it
to another workbook, the copied chart's source data still references the
worksheet in the original workbook.

For example, in Book1!Sheet1, the chart source data is 'Sheet1'!$A$1:$B$6.
When I copy the sheet to Book1, the chart source data is
'[Book1]Sheet1'!$A!1:$B!6.

Is there a way to have the source data in the copy reference the new
workbook or do I need to programmatically adjust the source data after
copying the sheet?

Thanks for the reply. In my case, however, the chart and its source
data are on the same worksheet so they do get copied together.

Any other ideas?

- Drew

"Grey Newt" wrote:

The graph of course needs data to read from, so if you don't want a link to
the host workbook, you will need to copy the graph sheet AND its data sheet
together (ie simultaneously) to the new workbook.

Sheets(Array("Data", "Chart")).Select
Sheets(Array("Data", "Chart")).Copy


Martin



"Drew Lettington" wrote:

I have a worksheet that contains one chart and and the chart's source data in
rows and columns in that sheet. If I select the worksheet and then move it
to another workbook, the copied chart's source data still references the
worksheet in the original workbook.

For example, in Book1!Sheet1, the chart source data is 'Sheet1'!$A$1:$B$6.
When I copy the sheet to Book1, the chart source data is
'[Book1]Sheet1'!$A!1:$B!6.

Is there a way to have the source data in the copy reference the new
workbook or do I need to programmatically adjust the source data after
copying the sheet?

OK - so do i assume that you don't want to copy the entire sheet - just the
embedded graph and its respective data ?

"Drew Lettington" wrote:


Thanks for the reply. In my case, however, the chart and its source
data are on the same worksheet so they do get copied together.

Any other ideas?

- Drew

"Grey Newt" wrote:

The graph of course needs data to read from, so if you don't want a link to
the host workbook, you will need to copy the graph sheet AND its data sheet
together (ie simultaneously) to the new workbook.

Sheets(Array("Data", "Chart")).Select
Sheets(Array("Data", "Chart")).Copy


Martin



"Drew Lettington" wrote:

I have a worksheet that contains one chart and and the chart's source data in
rows and columns in that sheet. If I select the worksheet and then move it
to another workbook, the copied chart's source data still references the
worksheet in the original workbook.

For example, in Book1!Sheet1, the chart source data is 'Sheet1'!$A$1:$B$6.
When I copy the sheet to Book1, the chart source data is
'[Book1]Sheet1'!$A!1:$B!6.

Is there a way to have the source data in the copy reference the new
workbook or do I need to programmatically adjust the source data after
copying the sheet?

I don't know if this is exactly what you are after, but there is a routine
in my XspandXL add-in designed to pull charts and their data into a new
book. I built this one for an environment in which I didn't want to give the
whole workbook to a publication production team, just the chart and the raw
data. What it does is copy the data as values, then links a new chart to the
exported values. There's a demo version on my site if you want to give it a
try. It's the export command in the Chart Browser tool.

Robin Hammond
www.enhanceddatasystems.com


"Grey Newt" wrote in message
...
OK - so do i assume that you don't want to copy the entire sheet - just
the
embedded graph and its respective data ?

"Drew Lettington" wrote:


Thanks for the reply. In my case, however, the chart and its source
data are on the same worksheet so they do get copied together.

Any other ideas?

- Drew

"Grey Newt" wrote:

The graph of course needs data to read from, so if you don't want a
link to
the host workbook, you will need to copy the graph sheet AND its data
sheet
together (ie simultaneously) to the new workbook.

Sheets(Array("Data", "Chart")).Select
Sheets(Array("Data", "Chart")).Copy


Martin



"Drew Lettington" wrote:

I have a worksheet that contains one chart and and the chart's source
data in
rows and columns in that sheet. If I select the worksheet and then
move it
to another workbook, the copied chart's source data still references
the
worksheet in the original workbook.

For example, in Book1!Sheet1, the chart source data is
'Sheet1'!$A$1:$B$6.
When I copy the sheet to Book1, the chart source data is
'[Book1]Sheet1'!$A!1:$B!6.

Is there a way to have the source data in the copy reference the new
workbook or do I need to programmatically adjust the source data
after
copying the sheet?

Currently, I select a range on the original sheet that includes the chart and
its source data. That range is copied and pasted into a sheet in another
workbook.

I believe we initially tried copying the entire but it had the same chart
issue and also got a warning from Excel that cells with more than 256
characters would be truncated. So, we decided on copying and pasting a range.

- Drew

"Grey Newt" wrote:

OK - so do i assume that you don't want to copy the entire sheet - just the
embedded graph and its respective data ?

"Drew Lettington" wrote:


Thanks for the reply. In my case, however, the chart and its source
data are on the same worksheet so they do get copied together.

Any other ideas?

- Drew

"Grey Newt" wrote:

The graph of course needs data to read from, so if you don't want a link to
the host workbook, you will need to copy the graph sheet AND its data sheet
together (ie simultaneously) to the new workbook.

Sheets(Array("Data", "Chart")).Select
Sheets(Array("Data", "Chart")).Copy


Martin



"Drew Lettington" wrote:

I have a worksheet that contains one chart and and the chart's source data in
rows and columns in that sheet. If I select the worksheet and then move it
to another workbook, the copied chart's source data still references the
worksheet in the original workbook.

For example, in Book1!Sheet1, the chart source data is 'Sheet1'!$A$1:$B$6.
When I copy the sheet to Book1, the chart source data is
'[Book1]Sheet1'!$A!1:$B!6.

Is there a way to have the source data in the copy reference the new
workbook or do I need to programmatically adjust the source data after
copying the sheet?