Excel update chart data source

[bigben]

I'm using Access to update some reports in Excel. The workbook I update has 3 sheets. Two of the sheets contain charts and the third sheet contains the data for the charts. The access function rewrites the content of the data sheet. However, I don't know how to get the two charts to automatically update the source data (in VBA) to the new data range - which could be larger or smaller than the original. Is there a way I can automatically say the source data will be all the data on a particular sheet - this would solve the problem.

All help would be much appreciated.

 

[Hasit]

How have you created the charts in the first place?

The charts should automatically update with the new data as soon as the data has changed. This includes the axes for the charts too, so for example, the range on say the Y axis is 0 - 5. If the source data changes to 15 - 40, the Y axis should change too. Automatically.