Results 1 to 10 of 10
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Import Dynamic Range Names (Excel 2000>)

    Hi all,
    I have dynamic range names in a workbook that I need in a series of other WB's. Can the range names be imported from the original WB into the current existing WB"s?
    Tx
    Regards,
    Rudi

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

    Re: Import Dynamic Range Names (Excel 2000>)

    If you copy a worksheet to another workbook, defined names will travel with it.
    You could write a macro that loops through all defined names in one workbook and creates similar ones in another workbook. You'd have to build in lots of checks though to make sure that the names make sense in their new home.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import Dynamic Range Names (Excel 2000>)

    >>> If you copy a worksheet to another workbook, defined names will travel with it.

    Tx, this will work well....

    I always though range names were associated with a workbook as a whole. So if I created the dynamic range on the first worksheet, and then copy the 3rd worksheet into a new workbook, will that name range will be available to the new workbook too? (PS: I know I can test it, but I ask to clarify the thread so others who read it will know the answer without having to test it themselves).
    Regards,
    Rudi

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

    Re: Import Dynamic Range Names (Excel 2000>)

    > PS: I know I can test it, but I ask to clarify the thread

    So why don't you test it and clarify the thread yourself? <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import Dynamic Range Names (Excel 2000>)

    OK...I did... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I could use any sheet and the range names copied across to the new workbook! However....when I view the range names in the new workbook, the range name contains linked references to the original workbook.!!

    Is there a way to avoid the links?
    IE: I have a range name called DRN, refers to =Offset(Sh1!A1,1,0, CountA(Sh1!A:A),1)
    After the copy of the sheet to a new workbook the reference is: =Offset(C:[Book1.xls]Sh1!A1,1,0, CountA(C:[Book1.xls]Sh1!A:A),1)
    Regards,
    Rudi

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

    Re: Import Dynamic Range Names (Excel 2000>)

    How did you copy the sheet? When I tried it, the name in the new workbook referred to the copied sheet, not to the original sheet.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import Dynamic Range Names (Excel 2000>)

    I right clicked on the worksheet tab and chose Move/Copy to New Workbook!
    Regards,
    Rudi

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

    Re: Import Dynamic Range Names (Excel 2000>)

    If you copy Sh1 to the new workbook, the name should be OK.

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

    Re: Import Dynamic Range Names (Excel 2000>)

    Name manager would help too, if you use the List function, you get the names listed in a worksheet. Copy that sheet to a new workbook and use the pickup function to (re)create the names you select. Of course you need to make sure the worksheets the names reference coincide of both workbooks...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import Dynamic Range Names (Excel 2000>)

    Ok. Thnks for the advice guys. That works good!
    Regards,
    Rudi

Posting Permissions

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