Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Apr 2008
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Defined names using Ranges (Excel 2003)

    My misery of grasping Excel's "Names.Add Name" continues. I have an attached macro to give a name to each one of several dynamic ranges. However it will not apply the name to both ranges.

    I am trying:
    Worksheets(SheetName).Names.Add Name:=RangeDefinedName, RefersTo:=usedRng, Visible:=True

    I think it might be that I have to add the sheet name in the reference but I cannot seem to understand the syntax of the function, specifically as it applies to ranges.

    I appeciate all the help I have gotten here.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Defined names using Ranges (Excel 2003)

    Your code defines two names, the first one is local to the sheet One and the second one is local to the sheet Two. So if the sheet One is active, you'll only "see" the first name, and if the sheet Two is active, you'll only "see" the second name.

    If you want the define two global names, change the line

    Worksheets(SheetName).Names.Add Name:=RangeDefinedName, RefersTo:=usedRng, Visible:=True

    to

    ActiveWorkbook.Names.Add Name:=RangeDefinedName, RefersTo:=usedRng, Visible:=True

    You'll then "see" both names regardless of the active sheet.

  3. #3
    Lounger
    Join Date
    Apr 2008
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defined names using Ranges (Excel 2003)

    Thanks again!

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defined names using Ranges (Excel 2003)

    Maybe my article on defined names helps clarify things a bit:
    http://www.jkp-ads.com/articles/excelnames.htm
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Lounger
    Join Date
    Apr 2008
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defined names using Ranges (Excel 2003)

    And thanks again!

Posting Permissions

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