Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 1
Default how to copy sheet with charts without link to original data

I have a worksheet with ten charts on it. I need to copy the worksheet with
all its data and related charts to new sheets or files so I have a page for
every day, with different data entered every day. But when I copy the
worksheet with embedded charts and paste it into a new file, the charts pull
their source data from the original worksheet, not the new one.
The source data Value box is written as such, ='03-09-10'!$D$12

any ideas?
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: how to copy sheet with charts without link to original data

Here's how you can copy a worksheet with charts without linking to the original data:
  1. Right-click on the worksheet tab that you want to copy and select "Move or Copy" from the menu.
  2. In the "Move or Copy" dialog box, select "Create a copy" and choose the location where you want to copy the worksheet to. Click "OK".
  3. Now, you have a copy of the worksheet with all its data and charts. However, the charts are still linked to the original data.
  4. To break the link between the charts and the original data, select the chart and click on the "Design" tab in the ribbon.
  5. In the "Data" group, click on "Edit Data". This will open the "Edit Data Source" dialog box.
  6. In the "Edit Data Source" dialog box, click on the "Switch Row/Column" button to switch the rows and columns of the chart data.
  7. Click on "OK" to close the dialog box. The chart will now be updated with the new data.
  8. Repeat steps 4-7 for all the charts in the copied worksheet.
  9. Save the copied worksheet as a new file or rename the copied worksheet tab to reflect the new date.

That's it! You now have a copy of the worksheet with all its data and charts, without any links to the original data. Let me know if you have any questions or need further assistance.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.charting
Sam Sam is offline
external usenet poster
 
Posts: 2
Default how to copy sheet with charts without link to original data


Hi Esty,

Probably wont help you, but I had similar problem, I posted few days
ago. Didn't resolved it yet, but Jon suggested a tool on his website.
Doesn't do all the job, but still half way there - allows you to
automate the renaming. Didnt tried it my self - I did it the hard way
renaming 3 sheets 50 tabs each (gosh there were 150 graphs :( ), - but
if it works it will save you some time

that was my thread:

http://www.microsoft.com/communities...sloc=en-us&p=1

the link to the tool:
http://peltiertech.com/WordPress/how...ries-formulas/

Not sure how helpful that is, but it is at least an option.
If you find any better (automated method) of renaming the series of the
chart, let us know. I'll appreciate it.

Sam

On 3/10/2010 11:15 AM, Esty wrote:
I have a worksheet with ten charts on it. I need to copy the worksheet with
all its data and related charts to new sheets or files so I have a page for
every day, with different data entered every day. But when I copy the
worksheet with embedded charts and paste it into a new file, the charts pull
their source data from the original worksheet, not the new one.
The source data Value box is written as such, ='03-09-10'!$D$12

any ideas?


  #4   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 563
Default how to copy sheet with charts without link to original data

Are you copying by Copy and Paste?
Here is a way that makes a new duplicate sheet
Open the two workbooks and in XL2003 use Windows | Arrange or in Xl2007 use
View |Arrange to have them side by side
If you are talking about one workbook just follow from here
Click on the tab of the worksheet holding the data and charts
Hold down the CTRL key and drag the tab from one book to the other (you will
see an icon looking like a piece of paper with a + sign) --- with one file
drag from one place to another in the tab line-up
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"Esty" wrote in message
...
I have a worksheet with ten charts on it. I need to copy the worksheet
with
all its data and related charts to new sheets or files so I have a page
for
every day, with different data entered every day. But when I copy the
worksheet with embedded charts and paste it into a new file, the charts
pull
their source data from the original worksheet, not the new one.
The source data Value box is written as such, ='03-09-10'!$D$12

any ideas?


  #5   Report Post  
Posted to microsoft.public.excel.charting
Sam Sam is offline
external usenet poster
 
Posts: 699
Default how to copy sheet with charts without link to original data

Just tried your suggestion. Didn't work for me. Copies the sheet with the
data, but not the chart. Or may be I am doing sth wrong? I followed you steps
in 2007, tried in 1 workbook dragging Sheet1 to the end - creates copy with
the data-no raph
Tried between 2 files - same result - copied sheet with data on it, but
didnt transfer the graph... Any Excel settings I am missing?

Is it possible this to be done with VBA, as instead "Sheet1" there is
dynamic reference to the sheets name in
..Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14")
something like the formula
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
My VBA knowledge is limited, and I cant make this to work so far...

Any suggestions will be appreciated

Thanks

Sam

"Bernard Liengme" wrote:

Are you copying by Copy and Paste?
Here is a way that makes a new duplicate sheet
Open the two workbooks and in XL2003 use Windows | Arrange or in Xl2007 use
View |Arrange to have them side by side
If you are talking about one workbook just follow from here
Click on the tab of the worksheet holding the data and charts
Hold down the CTRL key and drag the tab from one book to the other (you will
see an icon looking like a piece of paper with a + sign) --- with one file
drag from one place to another in the tab line-up
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"Esty" wrote in message
...
I have a worksheet with ten charts on it. I need to copy the worksheet
with
all its data and related charts to new sheets or files so I have a page
for
every day, with different data entered every day. But when I copy the
worksheet with embedded charts and paste it into a new file, the charts
pull
their source data from the original worksheet, not the new one.
The source data Value box is written as such, ='03-09-10'!$D$12

any ideas?


.



  #6   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 563
Default how to copy sheet with charts without link to original data

Sorry, I cannot see anyway to solve your problem
I have just repeated the exercise and it really does work
My chart is non SHeet2 and when I click the data series in the chart, the
formula bar displays
=SERIES(,Sheet2!$A$7:$A$11,Sheet2!$B$7:$B$11,1)
After by 'copy by dragging tab', on Sheet2 (2) I see
=SERIES(,Sheet2 (2)!$A$7:$A$11,Sheet2!$B$7:$B$11,1)
So the reference to the sheet does no seem to be holding it up
I cannot see anything in Options that would foul us up
Want to send me a sample file ?
Get my email addy from my website
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Sam" wrote in message
...
Just tried your suggestion. Didn't work for me. Copies the sheet with the
data, but not the chart. Or may be I am doing sth wrong? I followed you
steps
in 2007, tried in 1 workbook dragging Sheet1 to the end - creates copy
with
the data-no raph
Tried between 2 files - same result - copied sheet with data on it, but
didnt transfer the graph... Any Excel settings I am missing?

Is it possible this to be done with VBA, as instead "Sheet1" there is
dynamic reference to the sheets name in
.Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14")
something like the formula
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
My VBA knowledge is limited, and I cant make this to work so far...

Any suggestions will be appreciated

Thanks

Sam

"Bernard Liengme" wrote:

Are you copying by Copy and Paste?
Here is a way that makes a new duplicate sheet
Open the two workbooks and in XL2003 use Windows | Arrange or in Xl2007
use
View |Arrange to have them side by side
If you are talking about one workbook just follow from here
Click on the tab of the worksheet holding the data and charts
Hold down the CTRL key and drag the tab from one book to the other (you
will
see an icon looking like a piece of paper with a + sign) --- with one
file
drag from one place to another in the tab line-up
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"Esty" wrote in message
...
I have a worksheet with ten charts on it. I need to copy the worksheet
with
all its data and related charts to new sheets or files so I have a page
for
every day, with different data entered every day. But when I copy the
worksheet with embedded charts and paste it into a new file, the charts
pull
their source data from the original worksheet, not the new one.
The source data Value box is written as such, ='03-09-10'!$D$12

any ideas?


.

  #7   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 461
Default how to copy sheet with charts without link to original data

Is the chart embedded in the sheet? Or is it a standalone chart sheet?

If it's a chart sheet, then select the data worksheet, hold Ctrl and
select the chart sheet, then hold Ctrl and drag both to the other workbook.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 3/10/2010 5:01 PM, Sam wrote:
Just tried your suggestion. Didn't work for me. Copies the sheet with the
data, but not the chart. Or may be I am doing sth wrong? I followed you steps
in 2007, tried in 1 workbook dragging Sheet1 to the end - creates copy with
the data-no raph
Tried between 2 files - same result - copied sheet with data on it, but
didnt transfer the graph... Any Excel settings I am missing?

Is it possible this to be done with VBA, as instead "Sheet1" there is
dynamic reference to the sheets name in
.Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14")
something like the formula
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
My VBA knowledge is limited, and I cant make this to work so far...

Any suggestions will be appreciated

Thanks

Sam

"Bernard Liengme" wrote:

Are you copying by Copy and Paste?
Here is a way that makes a new duplicate sheet
Open the two workbooks and in XL2003 use Windows | Arrange or in Xl2007 use
View |Arrange to have them side by side
If you are talking about one workbook just follow from here
Click on the tab of the worksheet holding the data and charts
Hold down the CTRL key and drag the tab from one book to the other (you will
see an icon looking like a piece of paper with a + sign) --- with one file
drag from one place to another in the tab line-up
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


wrote in message
...
I have a worksheet with ten charts on it. I need to copy the worksheet
with
all its data and related charts to new sheets or files so I have a page
for
every day, with different data entered every day. But when I copy the
worksheet with embedded charts and paste it into a new file, the charts
pull
their source data from the original worksheet, not the new one.
The source data Value box is written as such, ='03-09-10'!$D$12

any ideas?


.

  #8   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 38
Default how to copy sheet with charts without link to original data

Sam
You need to enable an option for this to work if the charts are embedded
on the worksheets where the data is, rather than on a chart sheet of
their own.
Go to Excel options Advanced Cut, Copy and Paste section.
Enable "Cut, copy, and sort inserted objects with their parent cells"

Now use Bernards Ctrl-drag technique, or a normal right click "Move
or copy" to copy the sheet and the chart should update the formulas to
the worksheet it is embedded on (not back to the original).

If your charts are on worksheets other than the ones where their source
data is, then you need to do something similar to what Jon suggests for
chart sheets, ie to select the sheet with the chart on it and the sheet
with the data on it and copy them all at once. The new copied sheet with
the chart will then point at the new copied sheet with the data, rather
than the old one.

Hope this combination gets you sorted out.

Adam

On 10/03/2010 22:01, Sam wrote:
Just tried your suggestion. Didn't work for me. Copies the sheet with the
data, but not the chart. Or may be I am doing sth wrong? I followed you steps
in 2007, tried in 1 workbook dragging Sheet1 to the end - creates copy with
the data-no raph
Tried between 2 files - same result - copied sheet with data on it, but
didnt transfer the graph... Any Excel settings I am missing?

Is it possible this to be done with VBA, as instead "Sheet1" there is
dynamic reference to the sheets name in
.Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14")
something like the formula
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
My VBA knowledge is limited, and I cant make this to work so far...

Any suggestions will be appreciated

Thanks

Sam

"Bernard Liengme" wrote:

Are you copying by Copy and Paste?
Here is a way that makes a new duplicate sheet
Open the two workbooks and in XL2003 use Windows | Arrange or in Xl2007 use
View |Arrange to have them side by side
If you are talking about one workbook just follow from here
Click on the tab of the worksheet holding the data and charts
Hold down the CTRL key and drag the tab from one book to the other (you will
see an icon looking like a piece of paper with a + sign) --- with one file
drag from one place to another in the tab line-up
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


wrote in message
...
I have a worksheet with ten charts on it. I need to copy the worksheet
with
all its data and related charts to new sheets or files so I have a page
for
every day, with different data entered every day. But when I copy the
worksheet with embedded charts and paste it into a new file, the charts
pull
their source data from the original worksheet, not the new one.
The source data Value box is written as such, ='03-09-10'!$D$12

any ideas?


.

  #9   Report Post  
Junior Member
 
Posts: 1
Default

I know this is an old thread, but I just found a solution to this and I've been wrestling with this problem for months now so I wanted to share it:

1.Create a New Workbook
2. Copy the worksheet to the new workbook
(right click: Move or Copy, select create a copy and put it in the new workbook)
3. Change the name of the worksheet to its new name in the new workbook
4. Move the newly named worksheet back to the old workbook


The chart references will point to the data on the newly named worksheet.
  #10   Report Post  
Banned
 
Posts: 29
Default

Quote:
Originally Posted by jlrdouglas View Post
I know this is an old thread, but I just found a solution to this and I've been wrestling with this problem for months now so I wanted to share it:

1.Create a New Workbook
2. Copy the worksheet to the new workbook
(right click: Move or Copy, select create a copy and put it in the new workbook)
3. Change the name of the worksheet to its new name in the new workbook
4. Move the newly named worksheet back to the old workbook


The chart references will point to the data on the newly named worksheet.

You need to enable an option for this to work if the charts are embedded
on the worksheets where the data is, rather than on a chart sheet of
their own.
Go to Excel options Advanced Cut, Copy and Paste section.
Enable "Cut, copy, and sort inserted objects with their parent cells"


  #11   Report Post  
Junior Member
 
Posts: 1
Smile

Quote:
Originally Posted by martincrow View Post
You need to enable an option for this to work if the charts are embedded
on the worksheets where the data is, rather than on a chart sheet of
their own.
Go to Excel options Advanced Cut, Copy and Paste section.
Enable "Cut, copy, and sort inserted objects with their parent cells"
I was reading this today as I was having the same problem. However, I have found that if I use the "Links" option under the edit menu, and change the source of the offending link to the new workbook containing the copied data and charts, all the links are changed.

Regards
Ian
(using Excel 2003)
  #12   Report Post  
Junior Member
 
Posts: 2
Smile

Quote:
Originally Posted by AdamV[_2_] View Post
Sam
You need to enable an option for this to work if the charts are embedded
on the worksheets where the data is, rather than on a chart sheet of
their own.
Go to Excel options Advanced Cut, Copy and Paste section.
Enable "Cut, copy, and sort inserted objects with their parent cells"

Now use Bernards Ctrl-drag technique, or a normal right click "Move
or copy" to copy the sheet and the chart should update the formulas to
the worksheet it is embedded on (not back to the original).

If your charts are on worksheets other than the ones where their source
data is, then you need to do something similar to what Jon suggests for
chart sheets, ie to select the sheet with the chart on it and the sheet
with the data on it and copy them all at once. The new copied sheet with
the chart will then point at the new copied sheet with the data, rather
than the old one.

Hope this combination gets you sorted out.

Adam

On 10/03/2010 22:01, Sam wrote:
Just tried your suggestion. Didn't work for me. Copies the sheet with the
data, but not the chart. Or may be I am doing sth wrong? I followed you steps
in 2007, tried in 1 workbook dragging Sheet1 to the end - creates copy with
the data-no raph
Tried between 2 files - same result - copied sheet with data on it, but
didnt transfer the graph... Any Excel settings I am missing?

Is it possible this to be done with VBA, as instead "Sheet1" there is
dynamic reference to the sheets name in
.Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14")
something like the formula
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
My VBA knowledge is limited, and I cant make this to work so far...

Any suggestions will be appreciated

Thanks

Sam

"Bernard Liengme" wrote:

Are you copying by Copy and Paste?
Here is a way that makes a new duplicate sheet
Open the two workbooks and in XL2003 use Windows | Arrange or in Xl2007 use
View |Arrange to have them side by side
If you are talking about one workbook just follow from here
Click on the tab of the worksheet holding the data and charts
Hold down the CTRL key and drag the tab from one book to the other (you will
see an icon looking like a piece of paper with a + sign) --- with one file
drag from one place to another in the tab line-up
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


wrote in message
...
I have a worksheet with ten charts on it. I need to copy the worksheet
with
all its data and related charts to new sheets or files so I have a page
for
every day, with different data entered every day. But when I copy the
worksheet with embedded charts and paste it into a new file, the charts
pull
their source data from the original worksheet, not the new one.
The source data Value box is written as such, ='03-09-10'!$D$12

any ideas?


.
Legend

Been wondering what had happened, with my copying of sheets with graphs, all of a sudden didn't work, i went and played with Advaned settings didn't i, but now fixed again all copies over graph and Macro buttons that work. Cheers
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy Sheet to new Sheet and clear cells on original sheets Boiler-Todd Excel Discussion (Misc queries) 7 September 23rd 09 10:02 PM
???Replicate a sheet but chart still link to original sheet Jaylin Charts and Charting in Excel 1 October 9th 06 03:14 PM
How do I copy a worksheet without the link to the original workboo Reliabengr Excel Discussion (Misc queries) 1 August 31st 05 01:17 AM
Copy tabs(sheets) from workbook without link to original source Rich Ulichny Excel Discussion (Misc queries) 3 August 25th 05 02:11 AM
Copy tabs(sheets) from workbook without link to original source Rich Ulichny Links and Linking in Excel 2 August 9th 05 03:26 PM


All times are GMT +1. The time now is 01:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"