Excel - copy sheet to another workbook WITHOUT names and links

Question

I would like to copy sheets from one workbook to another, which is very convenient. However, I've noticed sadly, that even if one sheet is copied, the other stuff from the source workbook comes either. I mean both Names in Name manager as well as links in the Data section.

Before my name manager in Excel was look like this:

but after copying JUST 1 worksheet from another workbook, I have them nearly 17K!

Obviously, I know how to delete them, but not the amount like this. I tried the VBA code in the link below:

https://www.mrexcel.com/board/threads/copy-sheet-without-duplicating-named-ranges.543897/

https://www.extendoffice.com/documents/excel/1550-excel-delete-all-named-ranges.html

but the code doesn't work properly for the number of records like this. The program remains frozen for...1hour?

If I don't delete them I have two things, which makes me concerned.

One of them is the pop-up below:

which keeps coming out when clicking OK even several times. Thereafter I have the alerts, that Excel cannot update my links, which has been described here:

https://www.exceltip.com/tips/cant-update-some-of-the-links-in-workbook-right-now-how-to-solve-it.html

As it worst even the VBA code seems to not work due to lack of memory.

It shows: Runtime error: Out of stack space

https://www.mrexcel.com/board/threads/run-time-error-28-out-of-stack-space.1112053/

So in this event, I am asking: Is it possible to make a sheet copy in another workbook WITHOUT the stuff in Name manager?

Answer 1

• In your original workbook, go to Formulas and activate "show formulas"

• Click the upper left corner between row and colum numbering to select the whole sheet

• Press Ctrl+c to copy

• Open Notepad (or any text-only editor), paste everything. Select all and copy everything.

• Open your destination workbook, and paste everything.

• A warning will pop up about activating links. If you click "Disable", Excel will not link your data to the original Workbook. You will now have only the data of the cells, but no formatting and no links.

• To copy the formatting, go back to your original workbook, and copy everything.

• Go to your destination Workbook, click on the little arrow below the "paste" button. From the dropdown, from "Other paste options" select "Formatting (R)", which should be the left-most symbol (on the last row).

Sources: This tutorial

Answer 2

As far as I can reproduce (current Excel for Office 365): Two methods to copy a sheet:

(a) You likely(?) copy a sheet by right-click on sheets tab (Fig. 1) and say "copy to new workbook". This method takes the names of this (but not other) sheets with it.

(b) You might try and select the range (even whole sheet range) and simply copy/paste it to a new workbook ("manually" ctrl-c, ctrl-v, or by context menu mouse click, but not as in (a)). In case this range does not include formula references to any range, no name is copied (not listed in name manager, nor shown in the names field when selecting the respective range). However, if a name is referenced in a formula, only this name(s) is copied along with the sheet, but no other name of the same sheet (at least this works in my Excel version, current Office 365 - hope this works for you).

Obviously, one cannot omit names that are in use by formulas in the respective range/sheet to be copied - this may need more elaborate substitution by standard range descriptions (A1 to B3).

Answer 3

If you move the entire worksheet from one file to another, all names and other connections concerning that worksheet will also be moved. That's a feature, which I understand is causing issues in your case, but can be useful in other scenarios.

To avoid this, you can simply copy the entire sheet and paste it onto an empty sheet in a new file.

1. Select all (Top left corner before and above column names and row numbers res[ectively)
2. Copy
3. Paste in new file

Now, this will copy the entire worksheet along with the formats and formula. If you need to paste only values, or any other specific exclusion, then you can use paste special.

Follow first two steps above, and then use Paste Special command to open a dialogue box and select as per your requirement.

Hope this helps.