# Excel Macro/Updating Charts Monthly



## Nick2112 (Apr 12, 2006)

I have multiple charts that I have to update monthly and want to automate the process. I have figured out most of it but I still cannot get the macro to extend the chart cell reference +1 (the new months data). Here is what I want to update +1 (each time the macro is run it would add 1, i.e R18, R19, etc):

ActiveChart.SeriesCollection(3).Values = "=Charts!R15C43:R17C43"

Any help would be appreciated!!!


----------



## dheerajnagpal (Mar 30, 2005)

Hi there, 

Assume that the Y series Data is in table K6-K18 and the X series data is in J6-J18
Remember, the X series is in J6-J18

What you will have to do is to first create a dynamic range with an offset value. I am putting the example

=OFFSET(Expenditure!$J$6,0,0,'Allowance (to claim)'!$D$8,1)
This creates a range in expenditure sheet from J6 to the value taken from Allowance (to claim) sheet cell D8. The value D8 may be the month of the year so that if it is jan, it will be 1. Hence the range will be from J6 to J6.
If the value is Dec, the value will be 12 and the range will be come J6 to J18.

This way you can create a dynamic range. 

Now create a chart, put in the Y range value as K6:K18 and for X, put in the dynamic range. This will result in the chart getting updated each month. 

Note: While creating the range, use absolute references e.g. $J$6 and not J6.

I am in a hurry so am unable to attach a working example. Do let me know if any success. 
In case you aren't able to make much sense, let me know and i will try to build a working sheet for you. 

Cheers
Dheeraj


----------

