Convert a range into range name in each worksheet?

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Convert a range into range name in each worksheet?

  1. #1
    Board Regular
    Join Date
    Sep 2003
    Location
    Orange County
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Convert a range into range name in each worksheet?

     
    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

  2. #2
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,199
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert a range into range name in each worksheet?

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

  3. #3
    Board Regular
    Join Date
    Sep 2003
    Location
    Orange County
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert a range into range name in each worksheet?

    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...

  4. #4
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert a range into range name in each worksheet?

    Hi Jake,

    This has only been tested to a limited extent but it should serve as a starting point for doing what you wish.
    Code:
    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
    Richie

  5. #5
    Board Regular
    Join Date
    Sep 2003
    Location
    Orange County
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert a range into range name in each worksheet?

    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?

  6. #6
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert a range into range name in each worksheet?

    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.
    Richie

  7. #7
    Board Regular
    Join Date
    Sep 2003
    Location
    Orange County
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert a range into range name in each worksheet?

    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?

  8. #8
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert a range into range name in each worksheet?

    Hi Jake,

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

    Specifically, if you look at the line that the error arises on:
    Code:
    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:

    ******** ******************** ************************************************************************>
    Microsoft Excel - deleteme.xls___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    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

    [HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    or like this:
    Code:
    ThisWorkbook.Names.Add Name:=.Value, RefersTo:=.Offset(0, 1).Value
    HTH
    Richie

  9. #9
    Board Regular
    Join Date
    Sep 2003
    Location
    Orange County
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert a range into range name in each worksheet?

    Cool! Thanks for the lucid explanation Richie

  10. #10
    Board Regular
    Join Date
    Sep 2003
    Location
    Orange County
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert a range into range name in each worksheet?

      
    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:

    ******** ******************** ************************************************************************>
    Microsoft Excel - dasak.xls___Running: xl2002 XP : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    PL0102PL0102!$A$1:$G$305
    2
    PL0202PL0202!$A$1:$G$305
    3
    PL0302PL0302!$A$1:$G$305
    4
    PL0402PL0402!$A$1:$G$305
    5
    PL0502PL0502!$A$1:$G$305
    6
    PL0602PL0602!$A$1:$G$305
    7
    PL0702PL0702!$A$1:$G$305
    8
    PL0802PL0802!$A$1:$G$305
    9
    PL0902PL0902!$A$1:$G$305
    10
    PL1002PL1002!$A$1:$G$305
    11
    PL1102PL1102!$A$1:$G$305
    Sheet1

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.



    Thanks Richie

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com