Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Range Names and Linked Files (Excel 2000, SR1)

    Hi Gang,

    My colleague has a workbook with many named ranges (165 total). He has a subroutine (see below) that creates a new book and copies a sheet from it into the new book. The sheet in question only has six named ranges.

    <pre>Sub Instead()

    Dim ThisBook As Workbook, ThatBook As Workbook

    Set ThisBook = ActiveWorkbook
    Set ThatBook = Workbooks.Add

    With ThisBook
    .Activate
    .Sheets("WhatNot").Copy after:=ThatBook.Sheets("Sheet1")
    End With

    End Sub
    </pre>


    After this subroutine, the new book has the copy of the sheet, but it also has almost all (159 total) of the named ranges from the original. However, all but six also contain the workbook name as part of the range name (a-la ...
    <pre>=[YeOldeBook.xls]SomeOtherSheet!$A$1
    </pre>


    I tried duplicating this behavior in a chopped-down sample workbook for you all to look at, but I couldn't reproduce it. That is, only the names associated with the sheet being copied get carried over to the new book.

    I hope this explanation makes sense. I am very stumped, any advice?

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Range Names and Linked Files (Excel 2000, SR1)

    Copying a sheet from on workbook to another will copy range names from the source workbook.
    Instead of copying a sheet, have you considered selecting the entire source sheet and pasting the sheet into a blank sheet in the new workbook using pastespecial. You could also pastespecial the formatting etc. You would probably have to add code to adjust column widths in the destination if the source sheet uses different settings.

    zeddy

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

    Re: Range Names and Linked Files (Excel 2000, SR1)

    Hi Jim,

    AFAIK, named ranges belonging to other sheets are only copied if the sheet you are copying contains references to those names.

    So if you source sheet only contains two names Test1 and Test2, copying it will only transfer test1 and test2.
    If however you have a name Test3, that refers to another sheet and in your source sheet you have a formula that references this name (e.g. =test3), then that name gets copied over as well, but still references the source workbook.

    Clear as mud? <g>

    BTW: the code can be shortened:

    Sub Instead()
    ActiveWorkbook.Sheets("WhatNot").Copy
    End Sub
    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
  •