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:

 

[earlyd]

Have you tried using the Name Box rather than the menus - at least when initially creating them? Sorry if this is obvious - just wondering.

 

[Jake_Wrestler]

The name box is another way I try (in order to mix it up a bit) but doing that 240 plus times is still murder on the carpal tunnel...

 

[Richie(UK)]

Hi Jake,

This has only been tested to a limited extent but it should serve as a starting point for doing what you wish.

Sub NamesTest()
    Dim rngNames As Range, rngCell As Range
    
    With Sheet1
        Set rngNames = .Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
        'the range containing the names
        For Each rngCell In rngNames
            With rngCell
                If NameExists(.Value) Then ThisWorkbook.Names(.Value).Delete
                'if a name already exists then delete it
                ThisWorkbook.Names.Add Name:=.Value, RefersTo:="=" & .Offset(0, 1).Value
            End With
        Next rngCell
    End With
    
End Sub

Function NameExists(strName As String) As Boolean
    Dim nmTest As Name
    On Error Resume Next
    Set nmTest = ThisWorkbook.Names(strName)
    On Error GoTo 0
    If Not nmTest Is Nothing Then NameExists = True
End Function

HTH

 

[Jake_Wrestler]

THanks Ritchie, unfortunately I get the following error:

Compile error. Variable not defined.

It is referring to "Sheet1" on the third line...

I am assuming to use the macro I select the range with the names in it then run it, is this correct?

 

[Richie(UK)]

Hi Jake,

The "Sheet1" part is the sheet CodeName for the first sheet in a new workbook (which is what I used for testing). It looks as if you have deleted this particular sheet at some stage but that doesn't matter, it was just an example - change it to the CodeName of the sheet containing the names and 'refers to' details in your workbook or replace it with Worksheets("WhateverName"), where "WhateverName" is the name of your chosen sheet .

No need to select the range, the code will pick up the names from column A of the specified sheet.

 

[Jake_Wrestler]

Hi Richie,

After I created some named ranges in sheet1 and place their names in column A, I get the following error when I run it:

application-defined or object defined error

It is referring to this line:
ThisWorkbook.Names.Add Name:=.Value, RefersTo:="=" & .Offset(0, 1).Value

Maybe I am going about it wrong. I creatd the following named ranges

one =Sheet1!$D$3:$E$9

two =Sheet1!$C$7:$D$15

three =Sheet1!$C$9:$F$9

four =Sheet1!$D$5:$D$24

in sheet1 and put the names (one, two, three, four) in column A then run the macros, is this right? I am assuming it should take those named ranges and populate all the sheets with them, right?

 

[Richie(UK)]

Hi Jake,

Come on, look at these things as a challenge and try to figure out why they don't work :wink:

Specifically, if you look at the line that the error arises on:

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

what does it do?

It adds a name to the names collection of ThisWorkbook.
The name is taken from the value of the cell in column A.
The refers to is equal to "=" plus the value in the cell next to the one in column A.

So, if you are adding references with "=" at the beginning in column B then the code is effectively 'seeing double', try ditching one of the "=" from the start, like this:

deleteme.xls
	A	B	C	D
1	one	Sheet1!$D$3:$E$9
2	two	Sheet1!$C$7:$D$15
3	three	Sheet1!$C$9:$F$9
4	four	Sheet1!$D$5:$D$24
Sheet1

or like this:

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

HTH

 

[Jake_Wrestler]

Cool! Thanks for the lucid explanation Richie

 

[Jake_Wrestler]

Richie,

I am still getting the same error and I have been fiddling with it for a few hours. Any ideas? I set up the data like you suggested:

dasak.xls
	A	B	C	D
1	PL0102	PL0102!$A$1:$G$305
2	PL0202	PL0202!$A$1:$G$305
3	PL0302	PL0302!$A$1:$G$305
4	PL0402	PL0402!$A$1:$G$305
5	PL0502	PL0502!$A$1:$G$305
6	PL0602	PL0602!$A$1:$G$305
7	PL0702	PL0702!$A$1:$G$305
8	PL0802	PL0802!$A$1:$G$305
9	PL0902	PL0902!$A$1:$G$305
10	PL1002	PL1002!$A$1:$G$305
11	PL1102	PL1102!$A$1:$G$305
Sheet1

Thanks Richie