Creating a series of named ranges though VBA in a fast way

Dear Excel users,
I've recently learnt how to create charts that dynamically update to new sets of updated data, and I've found it pretty amazing.
The method I have used is through giving specific names to ranges and then using the OFFSET and COUNTA functions to make them match...however there is a problem with this method.
I am making graphs for a wide range of time series data and while the values on the X axis are the same for all the charts, of course the Y values vary for each chart and that means that I have to manually name all these ranges with different names and that takes a lot of time.

My Question is is there a way to do all this process of naming all the different ranges through a VBA code?
I am really new at VBA coding so please excuse me if this is a really stupid question :D Looking forward to your reply, thanks!

With VBA you can do almost everything. But without an example (your Excel file) it is hard to help you.

Oh you are right, sorry!

Attaching the file right now.
So as you can see I have managed to create a dynamic chart for one of the time series in the file, however I'd have to do the same for all the series there.
That's why I'd like to create something like a loop in VBA to create these charts in an automatic way...can you help me doing that?

Many Many Thanks

Check the sound
Book1 (3).xlsm

Hey lancer102rus, thanks a lot!

However the charts you managed to create do not seem dynamic to me, they don't use named ranges and the offset function so they won't update ifd I change the forecast start year...any chance you know how to code that too? :D

Thanks for your patience (I've never used VBA up to now so forgive me if this is really stupid....)

OK. Is that what you need?
Book1 (3).xlsm

Yes that's it thanks

Last thing, assume I want to do this for a more complex excel file with more sheets and data in it...what do I have to do create a macro like the one you called Module 1?
can I export this macro exactly as it is in the other excel sheet (which has more sheets on top of the one called 'intermediate_output')?

Can you suggest me a basic VBA textbook or website I can consult?

Manu many thanks

You can try to export this macro. May be it will work correct, may be not ))) depends on file.
About basic VBA I can`t suggest smthng exactly. May be http://www.excel-easy.com/vba.html