不需要的名称移动奇怪 [英] Unwanted range name moving weirdness

问题描述

2天后修改：更好的问题解决，固定的复制步骤和额外的结论;这也是更大的问题的一部分如何可靠地移动工作表 -

我需要将工作表的内容移动到同一工作簿中的新工作表的内容（请参阅背景这个相当愚蠢的练习）。我最初的猜测是使用 sourceSheet.Cells.Cut destSheet.Cells 。

但是，我的表格都有定义的标记名称，并在表格之间引用。当您执行此剪切/粘贴时，您将假定所有正在移动的数据的链接将保持原样，并且名称的所有用户都将被正确更新。我发现我的恐惧是，Excel将在某些情况下帮助添加和切换到工作簿范围的重复的工作表范围的重复。

这也发生在手工执行。请执行以下设置：

• Sheet1和Sheet 2都有一个名为Source的名称在A1上设置的名称


• Sheet1和Sheet2都通过公式= Source在B1中使用该名称。Sheet2还通过公式= Sheet1在C1中引用Sheet1的Source来源


• 现在选择Sheet1上的所有内容，并将其粘贴到Sheet3上

打开名称管理器时的效果是，源名称仍然定义为Sheet2和（现在）Sheet3上的工作表范围名称，但它仍然在Sheet1本身上定义。 Sheet1上的版本指向Sheet3！A1 ...单元格C1中的Sheet2的公式仍然使用此重定向Sheet1名称版本。

然而，由于我需要实际上'刷新'工作表（所以移动内容，然后删除原件），我需要采取的下一步是：

• 将旧的Sheet1从它的苦难中放出来，并且


• （这里不相关）给新表格旧的名字

Excel完成后，看到Sheet2仍然使用名称Sheet1！Source，显然有助于将其推广到工作簿范围名称Source，仍然指向Sheet3！A1。 C1中的Sheet2公式现在转换为 =<工作簿文件名>！Source ;即现在指的是工作簿特定的一个。

然而，在我的情况下，这是不可接受的，因为这个工作簿范围版本严重阻碍了我的解决方案的进一步行动，像再次复制纸张（现在将使Excel向用户询问在副本中需要使用哪些版本的这些名称）。我真的只想刷新表单，而不是随着时间的推移收集一百万个旧的骨架。

我已经尝试添加名称去重复代码，其中我会在Sheet3上保留页面范围的版本，并将工作簿范围限定在一个cq。即将面世的Sheet1（恢复正确的名称定义，就像所有这些）一样。然而，它将Sheet2！C1中的公式转换为 #NAME？，这是合乎逻辑的，因为我们刚刚将其使用的名称从它的脚下推开。现在我还可以添加额外的代码来扫描所有工作表上的所有公式以预先或之后修改公式，但这是不够的，因为例如。数据验证列表也可以使用名称 - 这很快就会成为我不想拥有的很多专门的代码。

因此，看起来移动表单的内容，与在这一点上使得范围名称为fork，其中叉的两个分支随后将被工作簿的不同部分使用。有一个混乱，换句话说...

有人有人建议如何绕过这个不幸的情况？

解决方案

您的方案不会导致我的Excel 2010的重复。但是我也不时观察到这一点。 >

您可以通过代码删除全局名称（将公式结果转换为#REF！），然后添加相同的页面范围名称。表格上的公式将会取代新的名称而不改变任何内容，重新计算就足够了。

这也适用于数据验证源。在图表中，我无法使用命名引用。至于可以使用名称的其他地方，只需测试...

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?

解决方案

Your scenario does not cause the duplication on my Excel 2010. However I also observed this from time to time.

You can delete the global name by code (turning formula results into #REF!), and afterwards add an identical sheet-scoped name. The formulas on the sheet will pick up the new name without changing anything, recalculation is sufficient.

This also works for data validation sources. In charts you cannot use named references as far as I know. As for other places where names can be used, just test...

这篇关于不需要的名称移动奇怪的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！