How do I copy a worksheet without creating local copies of workbook-scoped named formulas?

Question

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

Answer 1

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.

Answer 2

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'