Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a workbook with some workbook-scoped named formulas. When I copy a worksheet that has some (different) worksheet-scoped names, the copy ends up also having worksheet-scoped names that are identical (other than scope) to the workbook-scoped names.

Example: Say the workbook has workbook-scoped name 'a', the worksheet has worksheet-scoped name 'b'. The copy of the worksheet ends up with 'b' (as expected), and a worksheet-scoped name 'a' that refers to the same formula that the workbook-scoped 'a' does.

I guess I haven't used named formulas enough to have noticed that this is how Excel works, but it's definitely not how I want it to work. Does anyone know how to copy sheets without spewing extra names everywhere? (This includes the related question of how to copy a worksheet from onw workbook to another without also copying over all the workbook-scoped names from the source workbook.)

share|improve this question

2 Answers 2

up vote 1 down vote accepted

For the sake of completeness, here is my answer from your follow-up question that ended up working:

You should be able to select all of the cells in the original worksheet, copy them, and then "paste special" (Edit > Paste Special) in the target workbook. From within the "paste special" dialog, you can specify only formulas, values, etc. and omit style information.

share|improve this answer
    
I'll accept this too unless there is a more direct answer soon. Thanks again! –  jtolle Aug 3 '10 at 14:46
    
As with my other question, this answer gets me what I need for convenience in my case. If I were trying to automate this and copy code behind the worksheet, etc., it's not complete. Note that copying cells seems to copy local defined names that are actually used by the copied cells, but not any other local names. (In my case, though what I care about is not creating a local copy of a workbook-scoped name, and this satisfies that need.) –  jtolle Aug 7 '10 at 15:32

In current versions of Excel you can easily remove all the names scoped to the new worksheet through the Name Manager.

Open the name manager using either:

  • Old shortcut: Alt, I, N, D
  • Ribbon: Formulas -> (Defined Names) Name Manager

Then:

  • Sort by 'Scope' by clicking on the 'Scope' header
  • Select the first name that refers to the sheet in question
  • Hold 'Control' and click the last name that refers to the sheet in question
  • Click 'Delete'
  • Click 'Close'
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.