Edit after 2 days: better problem explanation, fixed reproduction steps and added extra conclusion; this is now also part of the bigger question of how to reliably move sheets about --
I need to move the content of a sheet to that of a new sheet in the same workbook (see the background of this rather silly exercise). My initial guess was to use sourceSheet.Cells.Cut destSheet.Cells.
However, my sheets all have sheet-scoped names defined, and they get referenced between the sheets. When you do this cut/paste, you'd assume all links to the data being moved would remain as-is, and all users of the names would be updated correctly. What I found out to my horror is that Excel will 'helpfully' add and switch to workbook-scoped duplicates of worksheet-scoped names under certain circumstances.
This also happens when performing this by hand. Take the following set-up:
- Sheet1 and Sheet 2 both have a sheet-specific name named "Source" set on A1
- Sheet1 and Sheet 2 both use that name in B1 via the formula "=Source"
- Sheet2 also refers to Sheet1's Source in C1 via the formula "=Sheet1!Source"
- Now select all the content on Sheet1, and paste it on Sheet3
The effect, when you open the Name Manager, is that the "Source" name is still defined as a sheet-scoped name on Sheet2 and (now) Sheet3, but it's also still defined on Sheet1 itself. The version on Sheet1 however points to Sheet3!A1... Sheet2's formula in cell C1 also still makes use of this redirecting Sheet1 name version.
However, since I need to actually 'refresh' the sheet (so move over the content, then delete the original), the next step I need to take is to:
- Put the old Sheet1 out of it's misery, and
- (not relevant here) Give the new sheet the old one's name
After this is done, Excel, seeing that Sheet2 still uses the name Sheet1!Source, apparently helpfully decides to promote it to a workbook-scoped name 'Source', still pointing to Sheet3!A1. Sheet2's formula in C1 is now transformed into =<workbook file name>!Source; i.e. it now refers to the workbook-specific one instead.
This is however unacceptable in my situation, since this workbook-scoped version is sorely hindering further actions of my solution, like making copies of sheets again (which will now make Excel question the user which version of these names it needs to use in the copy). And I really only want to 'refresh' the sheet, not over time collect a million old skeletons of it.
I already tried to add name de-duplicating code, where I'd keep the sheet-scoped version on Sheet3 and nuke the workbook-scoped one cq. the sheet-scoped one on soon-to-dismiss Sheet1 (restoring the proper name definitions as they were before all this). This however transforms the formula in Sheet2!C1 to #NAME?, which is logical, since we just shoved the name it uses from under it's feet. Now I could also add extra code to scan all formulas on all sheets to fix the formulas beforehand or afterwards, but that wouldn't be enough, since e.g. data validation lists can also use names - that quickly becomes a lot of specialized code I'd rather not have.
It thus seems that moving a sheet's content, separate from the sheet itself, makes the range names fork at that point, where both branches of the fork will be in use by different parts of the workbook afterwards. A mess in other words...
Does anyone have a suggestion on how I could bypass this unfortunate situation?
