+ Reply to Thread
Results 1 to 2 of 2

Change Source Data for Chart Moved to a New Workbook

  1. #1
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Change Source Data for Chart Moved to a New Workbook

    I am running a macro which moves selected sheets from a main workbook to a new workbook. Some of the moved sheets include graphs (note: these are 'not' pivot charts), the data source for which are on the moved sheets but use named ranges, however once moved the data source for the charts is still pointing back to the original workbook the sheets were moved from.

    For Example

    Source Workbook Data Source for Series Values in my Chart ='SourceFile.xlsm'!NamedRange

    The named range then refers to '=IF(MOD(COLUMN('Source Data'!$D$5:$O$5),1)=0,'Source Data'!$D$5:$O$5,NA())

    The Sheet, 'Source Data' is moved to the new workbook, along with the named ranges, however if I look at the data source for the chart in the new workbook with the original workbook open as well it refers back to the original workbook and if I look at the data source without the original workbook open the data source for the series values still refers back to the original workbook but I get an error message saying, 'Reference is not valid. Reference must be to an open worksheet'

    How can I change the data source using vba to point to the moved sheets in the new workbook so that when the new workbook is opened it doesn't refer back to the original workbook?

    This is my code as it stands

    Please Login or Register  to view this content.
    Many thanks

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Change Source Data for Chart Moved to a New Workbook

    Hi there,

    Here's a knee-jerk reaction after quickly reading your post - sorry if I've missed something, but it's worked for me in the past!

    Instead of copying the worksheets you need to a new workbook, why not make a copy of the entire workbook and then delete the worksheets you don't need? This approach won't be appropriate in many situations, but it's just a suggestion which might work.

    Hope this helps - please let me know how you get on.

    Regards,

    Greg M

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Change chart source data range
    By jprlimey in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-06-2015, 10:51 AM
  2. Chart Sheet Source data does not change when sheets are copied to new workbook
    By Henk Stander in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-08-2014, 03:01 AM
  3. Not able to change Excel chart data source
    By booo in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-21-2010, 02:15 PM
  4. Excel 2007 : Change chart source data in XL2007
    By normc in forum Excel General
    Replies: 2
    Last Post: 12-31-2009, 05:07 PM
  5. How change link source in long formula when source moved
    By Irina in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-28-2006, 02:30 AM
  6. change Line Chart source data in VB
    By Centurian77 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 03-20-2005, 12:52 PM
  7. How do I automatically update chart source data links for moved fi
    By Suzuki7 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-15-2005, 12:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1