Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Changing Range Names (2000)

    Situation: On worksheet 1, I have several (>25) ranges that are named, referencing data on that worksheet. What I need to do is make several copies this particular worksheet, but need to change the range names so that they will then correspond to the cells on the newly-copied worksheets. Is there a shortcut way to do this without having to go into the Insert-Names-Define and edit the each range (again, I have over 25 in each worksheet) name for worksheet 2 (and other copies) use?
    Thanks.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,265
    Thanks
    2
    Thanked 187 Times in 173 Posts

    Re: Changing Range Names (2000)

    Hi,
    Isn't that what happens by default if you do Edit-Move or Copy Sheet? Certainly works in Excel 2002 but I can't test on 2000. If not, I suspect that if you made the range names on your original sheet local to that sheet, then the copy should automatically adjust accordingly.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Changing Range Names (2000)

    Sorry, wasn't clear in my first post about my naming convention. On worksheet 1, I have named a typical range "Worksheet 1!Range 1", and the reference is "Worksheet 1!$A1:$A12". When I copy this worksheet, it retains the same name, but has the Worksheet 2!$A1:$A12 as the reference. What I am trying to do is change the name to "Worksheet 2!Range2".

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,265
    Thanks
    2
    Thanked 187 Times in 173 Posts

    Re: Changing Range Names (2000)

    I think you would have to code that. May I ask why you want the names changed as well? I would have thought it would be easier, if the sheets are all copies of each other, to use the same range name on each sheet.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Changing Range Names (2000)

    Good question, and one I should have addressed in my first post. The worksheets wach have unique names (say, different cities), and the ranges on each worksheet refer to different sets of data unique to that city. For example, assume I have named Worksheet 1 "New York", with the range A1:A12 data linked to cells in a separate file named "New York Data". When I then copy "New York" and rename it "Chicago", I then edit the actual ranges on Chicago to link to the "Chicago Data" file. However, after I do this, the range names on the Chicago worksheet still retain the name "New York", even though the reference is to the Chicago worksheet. What I want to do is make the name change on the Chicago worksheet ranges to Chicago.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Changing Range Names (2000)

    I am not clear what you are after.
    If I understand,(correct my mistakes)
    You have a worksheet with 25+ local names (Sheet1!Range1, instead of a just a name Range1)
    When you copy that sheet you get a new worksheet that has the same 25+ local names on it, but these refer to the names on the new sheet (your workbook now has 50+ names

    Now you want to rename all these names on the new sheet? What do you want to name them to?

    If this is what you want, I don't understand why you need to do this. The local names are only good on the sheets they are on, so what difference does it make if they are used again?

    If Sheet1!range1 is on sheet1 and Sheet2!range2 is on sheet2, you can't be on sheet 3 and refer to range1 or range2: they are only available to their own sheets (you made them local instead of global).

    Steve

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,265
    Thanks
    2
    Thanked 187 Times in 173 Posts

    Re: Changing Range Names (2000)

    Being lazy, what I would tend to do is call your range on each sheet something like CityData. That way you do not need to change it for each sheet. You can then refer to the ranges in formulas using syntax like <pre>='New York'!CityData+Chicago!CityData</pre>

    If that doesn't suit you, let me know and I will write some code to change the range names.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Changing Range Names (2000)

    Since you're copying sheets with range names it tends to get confusing what names are in your workbook. I'd suggest to download my Name Manager.
    It will greatly ease your search for what names are where and refer to what.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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