Changing chart source data with vba

[austin350s10]

I am trying to figure out a way using VBA to change the source value of a chart. I understand how to reassign the source value with VBA but I would like to take it one step further.

I want a way to have VBA find the current source value (specifically the row that it is referencing from the table of data), then reassign the the source data based on what it found.

I might be going about it the wrong way but it seems that this would work for what I am trying to do. If it helps any here is the idea of what I am trying to do.

I have a table set up as so:
(Years) 2011 2012 2013 2014 .....
(Items)
item1
item2
item3
....

I have several graphs that reference many different items on the table but all the graphs are to only reference a 3 year window. I would like to make sure the graphs only show 3 years worth of data when the work book is opened. This would ideally be set up as a workbook_open event so the user only sees the most current 3 year span on the graphs.

If someone has done something like this in the past maybe you would be willing to share the code as I cannot figure this out.

 

[Jon Peltier]

How many series in a chart? You can do this without VBA, with dynamically defined names. This example shows how to use the last N rows of a range for each series; it's a matter of rearranging the arguments of the OFFSET function to get the last N columns:

Chart the Last 12 Months