Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am using Excel 2007 & Windows 7.

    I have a 'master workbook' that I frequently make some changes to and I use it like a template; that is, I open the master and use it then save it under a different name so it is just like the master but with a different name. In this master workbook, I use many named ranges - some a block of cells and some a single cell. However, I have one worksheet in this workbook that contains none of these named ranges. When I want to copy this worksheet to another workbook that I have previously saved (which began as the master workbook and was saved to a different file name), I receive a lot of error notices that the workbook to which I am copying the worksheet already contains the named values. I am presented with the option of renaming the values in my worksheet. In fact, i receive such an error notice for each of the many named ranges (values) in my workbook.

    Is there any way to avoid this? Seems that since the worksheet I want to copy contains nothing but formulas with no reference to a named value, this should not be a problem.

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Don,

    I think you can get around this via Edit|Paste Special > Formulas. If there's cell formatting applied to the range copied from, you might need to couple that with Edit|Paste Special > Formats.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    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
    Named formulas/ranges are part of the workbook and not part of an individual worksheet and thus are in some ways part of EVERY worksheet. This is what allows their use in cond formatting, validation, etc when cells in the same sheet are required since they act like they are on that sheet.

    Unfortunately, you can get the problem that when a sheet is copied from one workbook to another all the names from the source workbook are all copied into the destination workbook. If the names already exist in the destination, you have to tell EXCEL what to do about each name.

    If the names have nothing to do with the sheet, you could try creating a new worksheet in the destination and copy all the CELLS from the source worksheet into the new empty destination worksheet.

    Another option would be to copy the source worksheet to a intermediate new blank workbook, and then delete all the names in this workbook (you could do it with a macro, but it is just as quick to run the single line (actually 3 LOCs) from the immediate window:
    for each nm in activeworkbook.Names:nm.delete:next

    Then you can copy this worksheet to the destination workbook.

    Steve

  4. #4
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the help. The work-around I have been using has been to click the top left of the worksheet (selecting the entire sheet) then copy/paste into the desired workbook. I will do the intermediate workbook as suggested.

Posting Permissions

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