Excel – How to copy a worksheet without creating local copies of workbook-scoped named formulas

microsoft excelworksheet-function

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

Best Answer

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.

Related Question