Dynamic range list issue - replace Offset function

Hi,

I have dynamic ranges set for drop down lists with OFFSET function, and they interfere with my macro for creating new sheets (copied Sheet1). Problem is that code runs very slow. Probably because OFFSET is a violatile function, and every time I create new sheet this OFFSET is recalculating.

I have read something about using INDEX and MATCH formula that works faster, but cannot set It right. I believe this way also works for numerical values only - and I have text values too.

This is my formula for one of my lists:

=OFFSET(Sheet1!$U$373;0;0;COUNTA(Sheet1!$U:$U)-1)

And tried to change that to (not working):

Sheet1!$U$373:INDEX(Sheet1!$U:$U,MATCH(9.99999999999999E+307,Sheet1!$U:$U))

If I remove dynamic ranges, code works o.k.

I want to keep dynamic range lists, so please show me how I could solve this problem !!

See attached sample (hit the "RUN" button If you want to see how slow It is) !

P.S. : I would rather have lists on separate sheet and have a Cmd button to show them, but my code for copying sheets slowed down again when I tried that. If you know how I could solve that too, please let me know ! Macro for copying is called Create_months.


Thanks for all your help !!

Regards, Luka