updating charts in worksheets

  • Hi,

    Can someone help me on how to update charts.

    I have a spreadsheet that have multiple worksheets which is updated daily. Each worksheets has its own data and a curve (in the same sheet). I have write a macro to update the data based on the daily source. However, I do not manage to update the curve related to the data.
    FYI, I'm using Excel 2007, but saving the file in *.xls format (some of the users not able to open .xlsx format).

    Please help.


  • Re: updating charts in worksheets

    Its hard to answer based on your description.

    It sounds like you need to use a dynamic range to refer to the updated data for your chart.

    A dynamic range is a formula based name that expands/contracts as data is added/removed from the range.

    There are three types of people in this world.
    Those who can count and those who can't.

  • Re: updating charts in worksheets

    I understand on the dynamic range. However, I have 12 worksheets, in which each of them has a chart (as an object in the sheet) that consists of 8 variables (or series). Assigning dynamic range is quite tedious. Is there anyway that I can update it faster?


  • Re: updating charts in worksheets

    I would opt for taking the time to create the dynamic ranges as inputs to the chart series.
    If all of the sheets are structured the same then you could created the first dynamic range, copy the formula and simply create new ranges by pasting in and adjusting the sheet reference and giving it a new name.

    Updating the charts with the named ranges is a snap and would only take a few minutes. IMO, VBA should be the last resort and would probably take as much time to write and debug as the named ranges.