+ Reply to Thread
Results 1 to 11 of 11

Copying a chart and unlinking it from the original workbook

  1. #1
    rmellison
    Guest

    Copying a chart and unlinking it from the original workbook

    Is there a way of copyiong a chart from one workbook to another and
    'unlinking' it, such that it does not update from the original worksheet if
    the original data is changed. Does it need to be copied as a picture or
    similar??

    Thanks in advance.

  2. #2
    Andy Pope
    Guest

    Re: Copying a chart and unlinking it from the original workbook

    Hi,

    Jon Peltier explains various way including the picture method.
    http://peltiertech.com/Excel/ChartsH...ChartData.html

    Cheers
    Andy

    rmellison wrote:
    > Is there a way of copyiong a chart from one workbook to another and
    > 'unlinking' it, such that it does not update from the original worksheet if
    > the original data is changed. Does it need to be copied as a picture or
    > similar??
    >
    > Thanks in advance.


    --

    Andy Pope, Microsoft MVP - Excel
    http://www.andypope.info

  3. #3
    rmellison
    Guest

    Re: Copying a chart and unlinking it from the original workbook

    Thanks Andy, Jon Peltier's site was very useful.

    I'm using the macro for multiple series with lots of data, however I get an
    error at line 13 (i think) saying type mismatch. I thought it may be because
    my chart has scatter and line chart data on it, so I deleted the line data
    and tried again, but got the same result. I'm somewhat of a novice at VBA,
    can you help any further??

    Error message in VB reads 'Runtime Error: '13': Type Mismatch'


    "Andy Pope" wrote:

    > Hi,
    >
    > Jon Peltier explains various way including the picture method.
    > http://peltiertech.com/Excel/ChartsH...ChartData.html
    >
    > Cheers
    > Andy
    >
    > rmellison wrote:
    > > Is there a way of copyiong a chart from one workbook to another and
    > > 'unlinking' it, such that it does not update from the original worksheet if
    > > the original data is changed. Does it need to be copied as a picture or
    > > similar??
    > >
    > > Thanks in advance.

    >
    > --
    >
    > Andy Pope, Microsoft MVP - Excel
    > http://www.andypope.info
    >


  4. #4
    Andy Pope
    Guest

    Re: Copying a chart and unlinking it from the original workbook

    Hi,

    That's an error type 13, which suggests the wrong type of value is being
    placed in a variable (eg. letter where a number is required).
    I have tried various things to re create you problem but can not.

    If you want email your workbook to me, off newsgroup, and I will take a
    look.

    Cheers
    Andy

    rmellison wrote:
    > Thanks Andy, Jon Peltier's site was very useful.
    >
    > I'm using the macro for multiple series with lots of data, however I get an
    > error at line 13 (i think) saying type mismatch. I thought it may be because
    > my chart has scatter and line chart data on it, so I deleted the line data
    > and tried again, but got the same result. I'm somewhat of a novice at VBA,
    > can you help any further??
    >
    > Error message in VB reads 'Runtime Error: '13': Type Mismatch'
    >
    >
    > "Andy Pope" wrote:
    >
    >
    >>Hi,
    >>
    >>Jon Peltier explains various way including the picture method.
    >>http://peltiertech.com/Excel/ChartsH...ChartData.html
    >>
    >>Cheers
    >>Andy
    >>
    >>rmellison wrote:
    >>
    >>>Is there a way of copyiong a chart from one workbook to another and
    >>>'unlinking' it, such that it does not update from the original worksheet if
    >>>the original data is changed. Does it need to be copied as a picture or
    >>>similar??
    >>>
    >>>Thanks in advance.

    >>
    >>--
    >>
    >>Andy Pope, Microsoft MVP - Excel
    >>http://www.andypope.info
    >>


    --

    Andy Pope, Microsoft MVP - Excel
    http://www.andypope.info

  5. #5
    rmellison
    Guest

    Re: Copying a chart and unlinking it from the original workbook

    Unfortunately can't email any data due to company restrictions. Spoil-sports!

    If the problem is the wrong type of variable maybe I need to check the data
    to make sure its in number format? Am I along the right lines? All of the
    data points are generated by formulae; not sure if this is relevent. Also,
    some of the data points are #N/A values, but I need to keep them in this form
    so that the original chart uses 'best fit' values across null values, rather
    than reverting to zero. Perhaps I need to modify the code to compensate for
    #N/A values?

    I also have a cell link for the series name (for use in the legend) which I
    will also need to unlink. Is this relevent?

    That's all I can think of really. As I said, still a bit of a novice with VBA.

    Thanks for your help.



    "Andy Pope" wrote:

    > Hi,
    >
    > That's an error type 13, which suggests the wrong type of value is being
    > placed in a variable (eg. letter where a number is required).
    > I have tried various things to re create you problem but can not.
    >
    > If you want email your workbook to me, off newsgroup, and I will take a
    > look.
    >
    > Cheers
    > Andy
    >
    > rmellison wrote:
    > > Thanks Andy, Jon Peltier's site was very useful.
    > >
    > > I'm using the macro for multiple series with lots of data, however I get an
    > > error at line 13 (i think) saying type mismatch. I thought it may be because
    > > my chart has scatter and line chart data on it, so I deleted the line data
    > > and tried again, but got the same result. I'm somewhat of a novice at VBA,
    > > can you help any further??
    > >
    > > Error message in VB reads 'Runtime Error: '13': Type Mismatch'
    > >
    > >
    > > "Andy Pope" wrote:
    > >
    > >
    > >>Hi,
    > >>
    > >>Jon Peltier explains various way including the picture method.
    > >>http://peltiertech.com/Excel/ChartsH...ChartData.html
    > >>
    > >>Cheers
    > >>Andy
    > >>
    > >>rmellison wrote:
    > >>
    > >>>Is there a way of copyiong a chart from one workbook to another and
    > >>>'unlinking' it, such that it does not update from the original worksheet if
    > >>>the original data is changed. Does it need to be copied as a picture or
    > >>>similar??
    > >>>
    > >>>Thanks in advance.
    > >>
    > >>--
    > >>
    > >>Andy Pope, Microsoft MVP - Excel
    > >>http://www.andypope.info
    > >>

    >
    > --
    >
    > Andy Pope, Microsoft MVP - Excel
    > http://www.andypope.info
    >


  6. #6
    Andy Pope
    Guest

    Re: Copying a chart and unlinking it from the original workbook

    I did test with NA() and blank cells both of these did not present a
    problem.

    Can you identify the actual line of code that produces the error? When
    the error dialog comes up choose Debug. The offending line should be
    hilited.

    How much data do you have in terms of rows and columns?

    Cheers
    Andy

    rmellison wrote:
    > Unfortunately can't email any data due to company restrictions. Spoil-sports!
    >
    > If the problem is the wrong type of variable maybe I need to check the data
    > to make sure its in number format? Am I along the right lines? All of the
    > data points are generated by formulae; not sure if this is relevent. Also,
    > some of the data points are #N/A values, but I need to keep them in this form
    > so that the original chart uses 'best fit' values across null values, rather
    > than reverting to zero. Perhaps I need to modify the code to compensate for
    > #N/A values?
    >
    > I also have a cell link for the series name (for use in the legend) which I
    > will also need to unlink. Is this relevent?
    >
    > That's all I can think of really. As I said, still a bit of a novice with VBA.
    >
    > Thanks for your help.
    >
    >
    >
    > "Andy Pope" wrote:
    >
    >
    >>Hi,
    >>
    >>That's an error type 13, which suggests the wrong type of value is being
    >> placed in a variable (eg. letter where a number is required).
    >>I have tried various things to re create you problem but can not.
    >>
    >>If you want email your workbook to me, off newsgroup, and I will take a
    >>look.
    >>
    >>Cheers
    >>Andy
    >>
    >>rmellison wrote:
    >>
    >>>Thanks Andy, Jon Peltier's site was very useful.
    >>>
    >>>I'm using the macro for multiple series with lots of data, however I get an
    >>>error at line 13 (i think) saying type mismatch. I thought it may be because
    >>>my chart has scatter and line chart data on it, so I deleted the line data
    >>>and tried again, but got the same result. I'm somewhat of a novice at VBA,
    >>>can you help any further??
    >>>
    >>>Error message in VB reads 'Runtime Error: '13': Type Mismatch'
    >>>
    >>>
    >>>"Andy Pope" wrote:
    >>>
    >>>
    >>>
    >>>>Hi,
    >>>>
    >>>>Jon Peltier explains various way including the picture method.
    >>>>http://peltiertech.com/Excel/ChartsH...ChartData.html
    >>>>
    >>>>Cheers
    >>>>Andy
    >>>>
    >>>>rmellison wrote:
    >>>>
    >>>>
    >>>>>Is there a way of copyiong a chart from one workbook to another and
    >>>>>'unlinking' it, such that it does not update from the original worksheet if
    >>>>>the original data is changed. Does it need to be copied as a picture or
    >>>>>similar??
    >>>>>
    >>>>>Thanks in advance.
    >>>>
    >>>>--
    >>>>
    >>>>Andy Pope, Microsoft MVP - Excel
    >>>>http://www.andypope.info
    >>>>

    >>
    >>--
    >>
    >>Andy Pope, Microsoft MVP - Excel
    >>http://www.andypope.info
    >>


    --

    Andy Pope, Microsoft MVP - Excel
    http://www.andypope.info

  7. #7
    rmellison
    Guest

    Re: Copying a chart and unlinking it from the original workbook

    Thanks for the continued help Andy, I have decided in the short term to use
    the copy puicture method on Jon's website, but I will need to be able to edit
    unlinked charts in the future so it'd be useful if we could nail this down.

    I have, in an attempt to get it working first of all, cut my chart to down
    to a single series of data with 200 x-values and 200 y-values, plotted as
    scatter chart with no legend or axis titles. I have copied over the original
    formulae for the data points with Paste Special | Values and deleted all
    cells containing #N/A, but i've kept them in the range. I've also re-copied
    the code from Jon's website into my VB editor (VBA Project
    (Workbook)\Microsoft Excel Objects\ThisWorkbook in the navigation window on
    the left).

    Now when I run it from within excel, I get an error message saying 400 and
    nothing else, with Ok and Help button but no Debug button. If I highlight the
    chart and run from within VB editor I get "Run-time error '1004':
    Application-defined or object-defined error". I am now utterly confused, and
    still unsure as to which line of code is my nemesis!

    Getting to the point where I say hang it all and just continue to use the
    copy picture method, but it would be a useful macro to have working...


    "Andy Pope" wrote:

    > I did test with NA() and blank cells both of these did not present a
    > problem.
    >
    > Can you identify the actual line of code that produces the error? When
    > the error dialog comes up choose Debug. The offending line should be
    > hilited.
    >
    > How much data do you have in terms of rows and columns?
    >
    > Cheers
    > Andy
    >
    > rmellison wrote:
    > > Unfortunately can't email any data due to company restrictions. Spoil-sports!
    > >
    > > If the problem is the wrong type of variable maybe I need to check the data
    > > to make sure its in number format? Am I along the right lines? All of the
    > > data points are generated by formulae; not sure if this is relevent. Also,
    > > some of the data points are #N/A values, but I need to keep them in this form
    > > so that the original chart uses 'best fit' values across null values, rather
    > > than reverting to zero. Perhaps I need to modify the code to compensate for
    > > #N/A values?
    > >
    > > I also have a cell link for the series name (for use in the legend) which I
    > > will also need to unlink. Is this relevent?
    > >
    > > That's all I can think of really. As I said, still a bit of a novice with VBA.
    > >
    > > Thanks for your help.
    > >
    > >
    > >
    > > "Andy Pope" wrote:
    > >
    > >
    > >>Hi,
    > >>
    > >>That's an error type 13, which suggests the wrong type of value is being
    > >> placed in a variable (eg. letter where a number is required).
    > >>I have tried various things to re create you problem but can not.
    > >>
    > >>If you want email your workbook to me, off newsgroup, and I will take a
    > >>look.
    > >>
    > >>Cheers
    > >>Andy
    > >>
    > >>rmellison wrote:
    > >>
    > >>>Thanks Andy, Jon Peltier's site was very useful.
    > >>>
    > >>>I'm using the macro for multiple series with lots of data, however I get an
    > >>>error at line 13 (i think) saying type mismatch. I thought it may be because
    > >>>my chart has scatter and line chart data on it, so I deleted the line data
    > >>>and tried again, but got the same result. I'm somewhat of a novice at VBA,
    > >>>can you help any further??
    > >>>
    > >>>Error message in VB reads 'Runtime Error: '13': Type Mismatch'
    > >>>
    > >>>
    > >>>"Andy Pope" wrote:
    > >>>
    > >>>
    > >>>
    > >>>>Hi,
    > >>>>
    > >>>>Jon Peltier explains various way including the picture method.
    > >>>>http://peltiertech.com/Excel/ChartsH...ChartData.html
    > >>>>
    > >>>>Cheers
    > >>>>Andy
    > >>>>
    > >>>>rmellison wrote:
    > >>>>
    > >>>>
    > >>>>>Is there a way of copyiong a chart from one workbook to another and
    > >>>>>'unlinking' it, such that it does not update from the original worksheet if
    > >>>>>the original data is changed. Does it need to be copied as a picture or
    > >>>>>similar??
    > >>>>>
    > >>>>>Thanks in advance.
    > >>>>
    > >>>>--
    > >>>>
    > >>>>Andy Pope, Microsoft MVP - Excel
    > >>>>http://www.andypope.info
    > >>>>
    > >>
    > >>--
    > >>
    > >>Andy Pope, Microsoft MVP - Excel
    > >>http://www.andypope.info
    > >>

    >
    > --
    >
    > Andy Pope, Microsoft MVP - Excel
    > http://www.andypope.info
    >


  8. #8
    Andy Pope
    Guest

    Re: Copying a chart and unlinking it from the original workbook

    Picture is the best way to go. The 1004 error is probably due to the
    length of the formula series exceeding 255 characters. A quick test of
    this is to select the data series and then with the cursor in the
    formula bar press F9. This will convert formula to values. If the
    conversion is incomplete you will get an error.

    Cheers
    Andy

    rmellison wrote:
    > Thanks for the continued help Andy, I have decided in the short term to use
    > the copy puicture method on Jon's website, but I will need to be able to edit
    > unlinked charts in the future so it'd be useful if we could nail this down.
    >
    > I have, in an attempt to get it working first of all, cut my chart to down
    > to a single series of data with 200 x-values and 200 y-values, plotted as
    > scatter chart with no legend or axis titles. I have copied over the original
    > formulae for the data points with Paste Special | Values and deleted all
    > cells containing #N/A, but i've kept them in the range. I've also re-copied
    > the code from Jon's website into my VB editor (VBA Project
    > (Workbook)\Microsoft Excel Objects\ThisWorkbook in the navigation window on
    > the left).
    >
    > Now when I run it from within excel, I get an error message saying 400 and
    > nothing else, with Ok and Help button but no Debug button. If I highlight the
    > chart and run from within VB editor I get "Run-time error '1004':
    > Application-defined or object-defined error". I am now utterly confused, and
    > still unsure as to which line of code is my nemesis!
    >
    > Getting to the point where I say hang it all and just continue to use the
    > copy picture method, but it would be a useful macro to have working...
    >
    >
    > "Andy Pope" wrote:
    >
    >
    >>I did test with NA() and blank cells both of these did not present a
    >>problem.
    >>
    >>Can you identify the actual line of code that produces the error? When
    >>the error dialog comes up choose Debug. The offending line should be
    >>hilited.
    >>
    >>How much data do you have in terms of rows and columns?
    >>
    >>Cheers
    >>Andy
    >>
    >>rmellison wrote:
    >>
    >>>Unfortunately can't email any data due to company restrictions. Spoil-sports!
    >>>
    >>>If the problem is the wrong type of variable maybe I need to check the data
    >>>to make sure its in number format? Am I along the right lines? All of the
    >>>data points are generated by formulae; not sure if this is relevent. Also,
    >>>some of the data points are #N/A values, but I need to keep them in this form
    >>>so that the original chart uses 'best fit' values across null values, rather
    >>>than reverting to zero. Perhaps I need to modify the code to compensate for
    >>>#N/A values?
    >>>
    >>>I also have a cell link for the series name (for use in the legend) which I
    >>>will also need to unlink. Is this relevent?
    >>>
    >>>That's all I can think of really. As I said, still a bit of a novice with VBA.
    >>>
    >>>Thanks for your help.
    >>>
    >>>
    >>>
    >>>"Andy Pope" wrote:
    >>>
    >>>
    >>>
    >>>>Hi,
    >>>>
    >>>>That's an error type 13, which suggests the wrong type of value is being
    >>>> placed in a variable (eg. letter where a number is required).
    >>>>I have tried various things to re create you problem but can not.
    >>>>
    >>>>If you want email your workbook to me, off newsgroup, and I will take a
    >>>>look.
    >>>>
    >>>>Cheers
    >>>>Andy
    >>>>
    >>>>rmellison wrote:
    >>>>
    >>>>
    >>>>>Thanks Andy, Jon Peltier's site was very useful.
    >>>>>
    >>>>>I'm using the macro for multiple series with lots of data, however I get an
    >>>>>error at line 13 (i think) saying type mismatch. I thought it may be because
    >>>>>my chart has scatter and line chart data on it, so I deleted the line data
    >>>>>and tried again, but got the same result. I'm somewhat of a novice at VBA,
    >>>>>can you help any further??
    >>>>>
    >>>>>Error message in VB reads 'Runtime Error: '13': Type Mismatch'
    >>>>>
    >>>>>
    >>>>>"Andy Pope" wrote:
    >>>>>
    >>>>>
    >>>>>
    >>>>>
    >>>>>>Hi,
    >>>>>>
    >>>>>>Jon Peltier explains various way including the picture method.
    >>>>>>http://peltiertech.com/Excel/ChartsH...ChartData.html
    >>>>>>
    >>>>>>Cheers
    >>>>>>Andy
    >>>>>>
    >>>>>>rmellison wrote:
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>>>Is there a way of copyiong a chart from one workbook to another and
    >>>>>>>'unlinking' it, such that it does not update from the original worksheet if
    >>>>>>>the original data is changed. Does it need to be copied as a picture or
    >>>>>>>similar??
    >>>>>>>
    >>>>>>>Thanks in advance.
    >>>>>>
    >>>>>>--
    >>>>>>
    >>>>>>Andy Pope, Microsoft MVP - Excel
    >>>>>>http://www.andypope.info
    >>>>>>
    >>>>
    >>>>--
    >>>>
    >>>>Andy Pope, Microsoft MVP - Excel
    >>>>http://www.andypope.info
    >>>>

    >>
    >>--
    >>
    >>Andy Pope, Microsoft MVP - Excel
    >>http://www.andypope.info
    >>


    --

    Andy Pope, Microsoft MVP - Excel
    http://www.andypope.info

  9. #9
    rmellison
    Guest

    Re: Copying a chart and unlinking it from the original workbook

    Yep, you're right. It works with no problems on a much smaller data set.
    However I was under the impression (from Jon's website) that the code
    specifically dealt with longer number data sets. Maybe mine was just too
    excessive!

    Thanks anyway for your efforts Andy. Will stick with the easy 'Copy Picture'
    option til I learn to be as good as you guys.

    "Andy Pope" wrote:

    > Picture is the best way to go. The 1004 error is probably due to the
    > length of the formula series exceeding 255 characters. A quick test of
    > this is to select the data series and then with the cursor in the
    > formula bar press F9. This will convert formula to values. If the
    > conversion is incomplete you will get an error.
    >
    > Cheers
    > Andy
    >
    > rmellison wrote:
    > > Thanks for the continued help Andy, I have decided in the short term to use
    > > the copy puicture method on Jon's website, but I will need to be able to edit
    > > unlinked charts in the future so it'd be useful if we could nail this down.
    > >
    > > I have, in an attempt to get it working first of all, cut my chart to down
    > > to a single series of data with 200 x-values and 200 y-values, plotted as
    > > scatter chart with no legend or axis titles. I have copied over the original
    > > formulae for the data points with Paste Special | Values and deleted all
    > > cells containing #N/A, but i've kept them in the range. I've also re-copied
    > > the code from Jon's website into my VB editor (VBA Project
    > > (Workbook)\Microsoft Excel Objects\ThisWorkbook in the navigation window on
    > > the left).
    > >
    > > Now when I run it from within excel, I get an error message saying 400 and
    > > nothing else, with Ok and Help button but no Debug button. If I highlight the
    > > chart and run from within VB editor I get "Run-time error '1004':
    > > Application-defined or object-defined error". I am now utterly confused, and
    > > still unsure as to which line of code is my nemesis!
    > >
    > > Getting to the point where I say hang it all and just continue to use the
    > > copy picture method, but it would be a useful macro to have working...
    > >
    > >
    > > "Andy Pope" wrote:
    > >
    > >
    > >>I did test with NA() and blank cells both of these did not present a
    > >>problem.
    > >>
    > >>Can you identify the actual line of code that produces the error? When
    > >>the error dialog comes up choose Debug. The offending line should be
    > >>hilited.
    > >>
    > >>How much data do you have in terms of rows and columns?
    > >>
    > >>Cheers
    > >>Andy
    > >>
    > >>rmellison wrote:
    > >>
    > >>>Unfortunately can't email any data due to company restrictions. Spoil-sports!
    > >>>
    > >>>If the problem is the wrong type of variable maybe I need to check the data
    > >>>to make sure its in number format? Am I along the right lines? All of the
    > >>>data points are generated by formulae; not sure if this is relevent. Also,
    > >>>some of the data points are #N/A values, but I need to keep them in this form
    > >>>so that the original chart uses 'best fit' values across null values, rather
    > >>>than reverting to zero. Perhaps I need to modify the code to compensate for
    > >>>#N/A values?
    > >>>
    > >>>I also have a cell link for the series name (for use in the legend) which I
    > >>>will also need to unlink. Is this relevent?
    > >>>
    > >>>That's all I can think of really. As I said, still a bit of a novice with VBA.
    > >>>
    > >>>Thanks for your help.
    > >>>
    > >>>
    > >>>
    > >>>"Andy Pope" wrote:
    > >>>
    > >>>
    > >>>
    > >>>>Hi,
    > >>>>
    > >>>>That's an error type 13, which suggests the wrong type of value is being
    > >>>> placed in a variable (eg. letter where a number is required).
    > >>>>I have tried various things to re create you problem but can not.
    > >>>>
    > >>>>If you want email your workbook to me, off newsgroup, and I will take a
    > >>>>look.
    > >>>>
    > >>>>Cheers
    > >>>>Andy
    > >>>>
    > >>>>rmellison wrote:
    > >>>>
    > >>>>
    > >>>>>Thanks Andy, Jon Peltier's site was very useful.
    > >>>>>
    > >>>>>I'm using the macro for multiple series with lots of data, however I get an
    > >>>>>error at line 13 (i think) saying type mismatch. I thought it may be because
    > >>>>>my chart has scatter and line chart data on it, so I deleted the line data
    > >>>>>and tried again, but got the same result. I'm somewhat of a novice at VBA,
    > >>>>>can you help any further??
    > >>>>>
    > >>>>>Error message in VB reads 'Runtime Error: '13': Type Mismatch'
    > >>>>>
    > >>>>>
    > >>>>>"Andy Pope" wrote:
    > >>>>>
    > >>>>>
    > >>>>>
    > >>>>>
    > >>>>>>Hi,
    > >>>>>>
    > >>>>>>Jon Peltier explains various way including the picture method.
    > >>>>>>http://peltiertech.com/Excel/ChartsH...ChartData.html
    > >>>>>>
    > >>>>>>Cheers
    > >>>>>>Andy
    > >>>>>>
    > >>>>>>rmellison wrote:
    > >>>>>>
    > >>>>>>
    > >>>>>>
    > >>>>>>>Is there a way of copyiong a chart from one workbook to another and
    > >>>>>>>'unlinking' it, such that it does not update from the original worksheet if
    > >>>>>>>the original data is changed. Does it need to be copied as a picture or
    > >>>>>>>similar??
    > >>>>>>>
    > >>>>>>>Thanks in advance.
    > >>>>>>
    > >>>>>>--
    > >>>>>>
    > >>>>>>Andy Pope, Microsoft MVP - Excel
    > >>>>>>http://www.andypope.info
    > >>>>>>
    > >>>>
    > >>>>--
    > >>>>
    > >>>>Andy Pope, Microsoft MVP - Excel
    > >>>>http://www.andypope.info
    > >>>>
    > >>
    > >>--
    > >>
    > >>Andy Pope, Microsoft MVP - Excel
    > >>http://www.andypope.info
    > >>

    >
    > --
    >
    > Andy Pope, Microsoft MVP - Excel
    > http://www.andypope.info
    >


  10. #10
    Andy Pope
    Guest

    Re: Copying a chart and unlinking it from the original workbook

    The code does reduce the number of number of decimal places used but
    apparently even this is not enough to keep the data values below the
    required length.
    Note my 255 length restriction should be approx 1000 characters, as
    pointed out on Jon's page.

    Cheers
    Andy

    rmellison wrote:
    > Yep, you're right. It works with no problems on a much smaller data set.
    > However I was under the impression (from Jon's website) that the code
    > specifically dealt with longer number data sets. Maybe mine was just too
    > excessive!
    >
    > Thanks anyway for your efforts Andy. Will stick with the easy 'Copy Picture'
    > option til I learn to be as good as you guys.
    >
    > "Andy Pope" wrote:
    >
    >
    >>Picture is the best way to go. The 1004 error is probably due to the
    >>length of the formula series exceeding 255 characters. A quick test of
    >>this is to select the data series and then with the cursor in the
    >>formula bar press F9. This will convert formula to values. If the
    >>conversion is incomplete you will get an error.
    >>
    >>Cheers
    >>Andy
    >>
    >>rmellison wrote:
    >>
    >>>Thanks for the continued help Andy, I have decided in the short term to use
    >>>the copy puicture method on Jon's website, but I will need to be able to edit
    >>>unlinked charts in the future so it'd be useful if we could nail this down.
    >>>
    >>>I have, in an attempt to get it working first of all, cut my chart to down
    >>>to a single series of data with 200 x-values and 200 y-values, plotted as
    >>>scatter chart with no legend or axis titles. I have copied over the original
    >>>formulae for the data points with Paste Special | Values and deleted all
    >>>cells containing #N/A, but i've kept them in the range. I've also re-copied
    >>>the code from Jon's website into my VB editor (VBA Project
    >>>(Workbook)\Microsoft Excel Objects\ThisWorkbook in the navigation window on
    >>>the left).
    >>>
    >>>Now when I run it from within excel, I get an error message saying 400 and
    >>>nothing else, with Ok and Help button but no Debug button. If I highlight the
    >>>chart and run from within VB editor I get "Run-time error '1004':
    >>>Application-defined or object-defined error". I am now utterly confused, and
    >>>still unsure as to which line of code is my nemesis!
    >>>
    >>>Getting to the point where I say hang it all and just continue to use the
    >>>copy picture method, but it would be a useful macro to have working...
    >>>
    >>>
    >>>"Andy Pope" wrote:
    >>>
    >>>
    >>>
    >>>>I did test with NA() and blank cells both of these did not present a
    >>>>problem.
    >>>>
    >>>>Can you identify the actual line of code that produces the error? When
    >>>>the error dialog comes up choose Debug. The offending line should be
    >>>>hilited.
    >>>>
    >>>>How much data do you have in terms of rows and columns?
    >>>>
    >>>>Cheers
    >>>>Andy
    >>>>
    >>>>rmellison wrote:
    >>>>
    >>>>
    >>>>>Unfortunately can't email any data due to company restrictions. Spoil-sports!
    >>>>>
    >>>>>If the problem is the wrong type of variable maybe I need to check the data
    >>>>>to make sure its in number format? Am I along the right lines? All of the
    >>>>>data points are generated by formulae; not sure if this is relevent. Also,
    >>>>>some of the data points are #N/A values, but I need to keep them in this form
    >>>>>so that the original chart uses 'best fit' values across null values, rather
    >>>>>than reverting to zero. Perhaps I need to modify the code to compensate for
    >>>>>#N/A values?
    >>>>>
    >>>>>I also have a cell link for the series name (for use in the legend) which I
    >>>>>will also need to unlink. Is this relevent?
    >>>>>
    >>>>>That's all I can think of really. As I said, still a bit of a novice with VBA.
    >>>>>
    >>>>>Thanks for your help.
    >>>>>
    >>>>>
    >>>>>
    >>>>>"Andy Pope" wrote:
    >>>>>
    >>>>>
    >>>>>
    >>>>>
    >>>>>>Hi,
    >>>>>>
    >>>>>>That's an error type 13, which suggests the wrong type of value is being
    >>>>>>placed in a variable (eg. letter where a number is required).
    >>>>>>I have tried various things to re create you problem but can not.
    >>>>>>
    >>>>>>If you want email your workbook to me, off newsgroup, and I will take a
    >>>>>>look.
    >>>>>>
    >>>>>>Cheers
    >>>>>>Andy
    >>>>>>
    >>>>>>rmellison wrote:
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>>>Thanks Andy, Jon Peltier's site was very useful.
    >>>>>>>
    >>>>>>>I'm using the macro for multiple series with lots of data, however I get an
    >>>>>>>error at line 13 (i think) saying type mismatch. I thought it may be because
    >>>>>>>my chart has scatter and line chart data on it, so I deleted the line data
    >>>>>>>and tried again, but got the same result. I'm somewhat of a novice at VBA,
    >>>>>>>can you help any further??
    >>>>>>>
    >>>>>>>Error message in VB reads 'Runtime Error: '13': Type Mismatch'
    >>>>>>>
    >>>>>>>
    >>>>>>>"Andy Pope" wrote:
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>>>Hi,
    >>>>>>>>
    >>>>>>>>Jon Peltier explains various way including the picture method.
    >>>>>>>>http://peltiertech.com/Excel/ChartsH...ChartData.html
    >>>>>>>>
    >>>>>>>>Cheers
    >>>>>>>>Andy
    >>>>>>>>
    >>>>>>>>rmellison wrote:
    >>>>>>>>
    >>>>>>>>
    >>>>>>>>
    >>>>>>>>
    >>>>>>>>>Is there a way of copyiong a chart from one workbook to another and
    >>>>>>>>>'unlinking' it, such that it does not update from the original worksheet if
    >>>>>>>>>the original data is changed. Does it need to be copied as a picture or
    >>>>>>>>>similar??
    >>>>>>>>>
    >>>>>>>>>Thanks in advance.
    >>>>>>>>
    >>>>>>>>--
    >>>>>>>>
    >>>>>>>>Andy Pope, Microsoft MVP - Excel
    >>>>>>>>http://www.andypope.info
    >>>>>>>>
    >>>>>>
    >>>>>>--
    >>>>>>
    >>>>>>Andy Pope, Microsoft MVP - Excel
    >>>>>>http://www.andypope.info
    >>>>>>
    >>>>
    >>>>--
    >>>>
    >>>>Andy Pope, Microsoft MVP - Excel
    >>>>http://www.andypope.info
    >>>>

    >>
    >>--
    >>
    >>Andy Pope, Microsoft MVP - Excel
    >>http://www.andypope.info
    >>


    --

    Andy Pope, Microsoft MVP - Excel
    http://www.andypope.info

  11. #11
    Jon Peltier
    Guest

    Re: Copying a chart and unlinking it from the original workbook

    No, it's supposedly 1000 characters (or 1024) in the whole formula, but
    that seems to have been evenly apportioned among the four arguments of
    the SERIES formula. 250 characters is only going to be enough for at
    most about 80 points, assuming two digits max and a comma between
    values. More practically it craps out around 20 to 40 points.

    My code merely trims a few meaningless digits to allow a few more points
    to be squeezed in. In general, I try to maintain the data for a chart
    somewhere, hidden if necessary. The storage requirements for this
    approach are minuscule, but the difficulties it saves are enormous.

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______


    Andy Pope wrote:
    > The code does reduce the number of number of decimal places used but
    > apparently even this is not enough to keep the data values below the
    > required length.
    > Note my 255 length restriction should be approx 1000 characters, as
    > pointed out on Jon's page.
    >
    > Cheers
    > Andy
    >
    > rmellison wrote:
    >
    >> Yep, you're right. It works with no problems on a much smaller data
    >> set. However I was under the impression (from Jon's website) that the
    >> code specifically dealt with longer number data sets. Maybe mine was
    >> just too excessive!
    >>
    >> Thanks anyway for your efforts Andy. Will stick with the easy 'Copy
    >> Picture' option til I learn to be as good as you guys.
    >>
    >> "Andy Pope" wrote:
    >>
    >>
    >>> Picture is the best way to go. The 1004 error is probably due to the
    >>> length of the formula series exceeding 255 characters. A quick test
    >>> of this is to select the data series and then with the cursor in the
    >>> formula bar press F9. This will convert formula to values. If the
    >>> conversion is incomplete you will get an error.
    >>>
    >>> Cheers
    >>> Andy
    >>>
    >>> rmellison wrote:
    >>>
    >>>> Thanks for the continued help Andy, I have decided in the short term
    >>>> to use the copy puicture method on Jon's website, but I will need to
    >>>> be able to edit unlinked charts in the future so it'd be useful if
    >>>> we could nail this down.
    >>>>
    >>>> I have, in an attempt to get it working first of all, cut my chart
    >>>> to down to a single series of data with 200 x-values and 200
    >>>> y-values, plotted as scatter chart with no legend or axis titles. I
    >>>> have copied over the original formulae for the data points with
    >>>> Paste Special | Values and deleted all cells containing #N/A, but
    >>>> i've kept them in the range. I've also re-copied the code from Jon's
    >>>> website into my VB editor (VBA Project (Workbook)\Microsoft Excel
    >>>> Objects\ThisWorkbook in the navigation window on the left).
    >>>> Now when I run it from within excel, I get an error message saying
    >>>> 400 and nothing else, with Ok and Help button but no Debug button.
    >>>> If I highlight the chart and run from within VB editor I get
    >>>> "Run-time error '1004': Application-defined or object-defined
    >>>> error". I am now utterly confused, and still unsure as to which line
    >>>> of code is my nemesis!
    >>>>
    >>>> Getting to the point where I say hang it all and just continue to
    >>>> use the copy picture method, but it would be a useful macro to have
    >>>> working...
    >>>>
    >>>>
    >>>> "Andy Pope" wrote:
    >>>>
    >>>>
    >>>>
    >>>>> I did test with NA() and blank cells both of these did not present
    >>>>> a problem.
    >>>>>
    >>>>> Can you identify the actual line of code that produces the error?
    >>>>> When the error dialog comes up choose Debug. The offending line
    >>>>> should be hilited.
    >>>>>
    >>>>> How much data do you have in terms of rows and columns?
    >>>>>
    >>>>> Cheers
    >>>>> Andy
    >>>>>
    >>>>> rmellison wrote:
    >>>>>
    >>>>>
    >>>>>> Unfortunately can't email any data due to company restrictions.
    >>>>>> Spoil-sports!
    >>>>>>
    >>>>>> If the problem is the wrong type of variable maybe I need to check
    >>>>>> the data to make sure its in number format? Am I along the right
    >>>>>> lines? All of the data points are generated by formulae; not sure
    >>>>>> if this is relevent. Also, some of the data points are #N/A
    >>>>>> values, but I need to keep them in this form so that the original
    >>>>>> chart uses 'best fit' values across null values, rather than
    >>>>>> reverting to zero. Perhaps I need to modify the code to compensate
    >>>>>> for #N/A values?
    >>>>>>
    >>>>>> I also have a cell link for the series name (for use in the
    >>>>>> legend) which I will also need to unlink. Is this relevent?
    >>>>>>
    >>>>>> That's all I can think of really. As I said, still a bit of a
    >>>>>> novice with VBA.
    >>>>>>
    >>>>>> Thanks for your help.
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>> "Andy Pope" wrote:
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>>> Hi,
    >>>>>>>
    >>>>>>> That's an error type 13, which suggests the wrong type of value
    >>>>>>> is being placed in a variable (eg. letter where a number is
    >>>>>>> required).
    >>>>>>> I have tried various things to re create you problem but can not.
    >>>>>>>
    >>>>>>> If you want email your workbook to me, off newsgroup, and I will
    >>>>>>> take a look.
    >>>>>>>
    >>>>>>> Cheers
    >>>>>>> Andy
    >>>>>>>
    >>>>>>> rmellison wrote:
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>>> Thanks Andy, Jon Peltier's site was very useful.
    >>>>>>>>
    >>>>>>>> I'm using the macro for multiple series with lots of data,
    >>>>>>>> however I get an error at line 13 (i think) saying type
    >>>>>>>> mismatch. I thought it may be because my chart has scatter and
    >>>>>>>> line chart data on it, so I deleted the line data and tried
    >>>>>>>> again, but got the same result. I'm somewhat of a novice at VBA,
    >>>>>>>> can you help any further??
    >>>>>>>>
    >>>>>>>> Error message in VB reads 'Runtime Error: '13': Type Mismatch'
    >>>>>>>>
    >>>>>>>>
    >>>>>>>> "Andy Pope" wrote:
    >>>>>>>>
    >>>>>>>>
    >>>>>>>>
    >>>>>>>>
    >>>>>>>>
    >>>>>>>>> Hi,
    >>>>>>>>>
    >>>>>>>>> Jon Peltier explains various way including the picture method.
    >>>>>>>>> http://peltiertech.com/Excel/ChartsH...ChartData.html
    >>>>>>>>>
    >>>>>>>>> Cheers
    >>>>>>>>> Andy
    >>>>>>>>>
    >>>>>>>>> rmellison wrote:
    >>>>>>>>>
    >>>>>>>>>
    >>>>>>>>>
    >>>>>>>>>
    >>>>>>>>>> Is there a way of copyiong a chart from one workbook to
    >>>>>>>>>> another and 'unlinking' it, such that it does not update from
    >>>>>>>>>> the original worksheet if the original data is changed. Does
    >>>>>>>>>> it need to be copied as a picture or similar??
    >>>>>>>>>>
    >>>>>>>>>> Thanks in advance.
    >>>>>>>>>
    >>>>>>>>>
    >>>>>>>>> --
    >>>>>>>>>
    >>>>>>>>> Andy Pope, Microsoft MVP - Excel
    >>>>>>>>> http://www.andypope.info
    >>>>>>>>>
    >>>>>>>
    >>>>>>> --
    >>>>>>>
    >>>>>>> Andy Pope, Microsoft MVP - Excel
    >>>>>>> http://www.andypope.info
    >>>>>>>
    >>>>>
    >>>>> --
    >>>>>
    >>>>> Andy Pope, Microsoft MVP - Excel
    >>>>> http://www.andypope.info
    >>>>>
    >>>
    >>> --
    >>>
    >>> Andy Pope, Microsoft MVP - Excel
    >>> http://www.andypope.info
    >>>

    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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