Convert a range into range name in each worksheet?

[Jake_Wrestler]

I often find myself often going into Insert -> Name -> Define and tediously changing my range names and parameters there. It is awfully time consuming.

Really I am just looking for a method to boost my effieciency.

In a perfect world I imagine a macro where I have two columns, one for the name of the range (i.e., ThompsonJune2001, ThompsonJune2002, MilfordJune2001, MilfordJune2002, etc. You can see how this could be time consuming if I have 4 years of monthly data from 5 or more stores [4 X 12 X 5 = 240 range names!!!]) and the other where I can specify the actual corresponding range (i.e., '6-2001'!$A$1:$AA$48, '6-2002'!$A$1:$AA$48, etc.).

From these two columns of input, the macro would create these named ranges on the fly. Is this macro possible??

If you think of an improvement if you can see what I am doing or any other suggestions for other ways that will help to boost my efficiency in creating name ranges like this.

thanks ray:

 

[Tana Lee]

Aloha...

As usual I don't have access to Excel at the moment, but I'm thinking of something in Names where you can define the range by column, row. or column and row...

Anybody know where it is right off? I'm thinking Insert --> Names --> Create. A box should pop up asking what you want to use for the name.

Would this be of any help to you?

Tana-Lee

 

[Jake_Wrestler]

Tana,
Yes, insert --> name --> define unfortunately it isn't all that fast if I have, say, 140 named ranges to define. This macro that Richie has helped me with would simply take the name from one column and the actual range from another column and create the named range. Unfortunately, I keep getting an error everytime I run it that reads:

Run time error '1004':
Application defined or object defined error

When I debug, the debugger points to this line in the macro:

ThisWorkbook.Names.Add Name:=.Value, RefersTo:="=" & .Offset(0, 1).Value

I can't seem to figure it out

 

[Jake_Wrestler]

Anyone ray: have any ideas how I can take two columns (one with names and the next with the actual ranges) and create named ranges with a macro? Otherwise I will be doing the long process of typing it 76 times... thank you for any insights! (my carpal tunnel thanks you too)

 

[Tana Lee]

Aloha Part II,

I still don't have Excel nearby, but I wasn't referring to Name Define.

Try Insert-->Name-->CREATE.

I think this will help you out. Really I do.

Tana-Lee

 

[dolphin]

Aaron Blood’s site has what you are talking about

Check out
Name_mgr_zip

http://www.xl-logic.com/pages/vba.html

 

[Jake_Wrestler]

Aloha Tana, you are dead on. Thanks a zillion!!!