Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Copying charts from one sheet to another

    I have created a worksheet with data, formatting, and two charts. I would like to copy this sheet to another sheet within the workbook. Whichever way I copy this one sheet to create a new sheet, the new sheet does not include the charts.

    When I manually Copy+Paste the charts to the newly created (but chartless) sheet, the chart objects all refer to the original data. I can't get Find/Replace to Replace the sheet name so I revert to re-creating parts of the chart or manually edit the chart element formulas.

    Is there some tip or trick to getting the charts to copy along with the rest of the sheet? Or perhaps some trick to Find/Replace the sheetnames in the chart elements? (all the data are located in the same range)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    right-click the sheet tabs
    Move or copy
    Select "create a copy"
    [ok]
    It creates a copy of the sheet with the chart with the chart linked to the data on the copied sheet

    If you just copy a part of the sheet with the chart (even if copy the data with it) the chart will remain "linked" to the original source.

    If you just want the chart and data copied into a new sheet, you can copy the sheet and delete the rest of the data, or you can copy the sheet and move the data and chart section to a different sheet and they will remain linked.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    See, that's what I'm missing.

    When I try Move or Copy - Create a Copy my Excel does not create (or copy) the chart along with the new sheet.

    What's up with that?

    using Excel 2007 SP3
    Last edited by Arcturus16a; 2012-01-10 at 19:07. Reason: indicating version used

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I have never used XL2007 so can not confirm the issue, though I recall reading some chart bugs in it. Perhaps the routines by Peltier can workaround the issues
    http://peltiertech.com/WordPress/cha...oved-routines/

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I tried the macro Program to Modify the Active Chart it looks promising to help Find/Replace the Sheetname references but it errors-out at mySrs.Formula = strTemp.

    I only need to change the sheetname since the data range from sheet to sheet will be identical. I also made sure (as the author says) to use identical text case between the actual sheet name and the entry typed into the VBA dialog box.

    I had a space included in my sheet name (the new sheet). I took that (the space) out and re-ran the macro. Works great. So no spaces, probably no special characters and identical text case.

    Thanks for the link Steve. Not sure whats up with my version of Excel, but I'll be using Peltier's macro lots.
    Last edited by Arcturus16a; 2012-01-10 at 20:34.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You are very welcome. I am glad I could help with the workaround.

    The space may be problematic (and may be fixable in the code) since names with spaces require a single quote (') to start and end the sheet name when referencing them. They can be used with names without spaces (but XL typically will remove them as unneeded, which is why they don't matter). They could be added to the replacement string before the replacement.

    It is probably easier to rename them without the spaces if the code is working now). You can always rename the sheet later to include them. One the links are re-done the chart names will be updated automatically when the sheet names are changed manually to add the space.

    But what the problem with Xl2007 is, I speculate, is a bug in the program that was fixed in xl2010...

    Steve

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    The reason why your chart isn't being copied is because your file has the "Cut, copy and sort inserted objects.." turned off.

    To fix this:
    Hit the microsoft blob thingy in the top left corner to display the panel of recent documents etc.
    In this panel, at the bottom, is a button labelled [Excel Options]
    Click this to display choices for your excel options, and select the one that says 'Advanced' (it's the fifth one in the list).
    Then, look for the second section labelled "Cut, copy, and paste'.
    Then make sure the (third) option checkbox labelled 'Cut, Copy, and sort inserted objects with their parent cells' is ticked.
    Click [OK] to finish.
    You should now be able to copy the sheet as instructed before, this time the Chart object will be included.
    When I copy a sheet, I hold down the [Ctrl] button on the keyboard and then use the mouse to point to the tab sheet I want to copy and drag it to where I want the new sheet inserted. I then rename the copied sheet as required.

    zeddy

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thats so cool! Thanks Zeddy!

    But wait! Are there unforseen consequences?

    Excel Help describes the option as follows:
    Cut, copy, and sort inserted objects with their parent cells Keeps graphic objects, buttons, text boxes, drawn objects, and pictures with their associated cells whenever you cut, copy, filter, or sort on a worksheet.

    Sounds all good but is there a downside?

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    No downside really - this is the default mode I believe.
    But, if you select a block of cells to copy somewhere else for example and the block includes a partially overlapping object (e.g. a macro button etc) then the 'object' will also be copied and pasted as well as your cells.
    In most cases you can tell when this happens.
    However, I have seen spreadsheets where there seems to be a single 'object', e.g. a macro button etc, when in fact there are dozens of the object overlaid exactly on top of each other.
    So sometimes it is neccessary to turn the 'cut,copy objects with cells' to OFF before you do the copy and paste operation.

    zeddy

  10. #10
    New Lounger
    Join Date
    Mar 2016
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    thank you!

    Thank you very much for your helpful reply. I was having exactly the same problem after moving to a new machine and didn't think to look at the setting you described - problem solved!

    Veronica

    Quote Originally Posted by zeddy View Post
    The reason why your chart isn't being copied is because your file has the "Cut, copy and sort inserted objects.." turned off.

    To fix this:
    Hit the microsoft blob thingy in the top left corner to display the panel of recent documents etc.
    In this panel, at the bottom, is a button labelled [Excel Options]
    Click this to display choices for your excel options, and select the one that says 'Advanced' (it's the fifth one in the list).
    Then, look for the second section labelled "Cut, copy, and paste'.
    Then make sure the (third) option checkbox labelled 'Cut, Copy, and sort inserted objects with their parent cells' is ticked.
    Click [OK] to finish.
    You should now be able to copy the sheet as instructed before, this time the Chart object will be included.
    When I copy a sheet, I hold down the [Ctrl] button on the keyboard and then use the mouse to point to the tab sheet I want to copy and drag it to where I want the new sheet inserted. I then rename the copied sheet as required.

    zeddy

  11. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Veronica

    Welcome to the Lounge as a new poster.
    I've had a lot of help from this Lounge and the people here are utterly delightful.

    zeddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •