Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Silicon Valley, California, USA
    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
    .Sheets("WhatNot").Copy after:=ThatBook.Sheets("Sheet1")
    End With

    End Sub

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

    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
    Newcazzle, UK
    Thanked 466 Times in 444 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.


  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Weert, Limburg, Netherlands
    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()
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    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