Thread: dynamic chart with VBA code

Can anyone tell me how to use VBA code for the dynamic chart? I have 30 charts need to be plotted as dynamic chart. I know how to do it manually but it is very time consuming. However, there is always an error message for defining the name range.
Thanks!
Wei

Hi there,

It's possible to manipulate charts using VBA, but I've always found it to be both complicated and inflexible.

My preferred alternative when charting data sets which have the same structure is to use a single chart populated from a single range. This single range is then populated from the various data sets by using functions such as INDIRECT and LOOKUP. This allows you to achieve what you want within Excel itself.

Hope this suggestion helps - let me know if you need anything further.

Regards,

Greg M

Hi again Wei,

The attached workbook shows a very simple example of what I'm talking about.

The chart always takes its data from the same (highlighted) range, but the VALUES in the highlighted range are appropriate to whichever year is selected from the dropdown list at the start of the highlighted range. This is achieved by using the VLOOKUP function.

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

Best regards,

Greg M