I have a chart in a spreadsheet which correlates to data in the same workbook. As the input data changes the graphical output (the chart) updates itself.
My problem comes when I simply increase the size of the spreadsheet. My spreadsheet now is utilizing around 60k rows, instead of around 10k, and now the chart refuses to automatically update it. To 'refresh' the chart I have to re-select the chart data for each series. This is cumbersome, but I have attached this to macros activated by a command button to make it easier and quicker.
Although not great (the chart should update automatically), this could be a working solution for me, however the macro to re-select the series 2 data is not working for some reason. The coding is as follows:
ActiveChart.SeriesCollection(2).XValues = "=GUI!R59C11"
ActiveChart.SeriesCollection(2).Values = "=GUI!R60C11"
My data for series 2 is a single point with x and y co-ordinates at K59 and K60 in worksheet 'GUI'. The error message says it cannot obtain the x value co-ordinate and the whole first line shows yellow in the VBA editor. This is exactly the same code as used for series 1, 3 and 4 of my chart (except for different cell references, obviously), so why won't this work???
Also - I am using excel 2003 but have tried EXACTLY the same workbook on excel 2007 and 2010 and not only does the chart update automatically with in excess of 60k rows, but when I run the macro, there is no error!!! How can this be and what are the problems Excel 2003 seems to be having?!?!?!?
MANY thanks in advance!!
This is probably because the Excel 2007/2010 version will hold the data as a table. I would suggest creating a dynamic named range for the data and use that. Can you post the workbook?