excel - 切换工作表名称而不更新对这些工作表的引用

Question

我的文件有两个相同的工作表供用户输入两组不同的假设变量，称为“InputA”和“InputB”。我想快速切换将哪个输入表馈送到模型的其他表中。

使用 Find and Replace 花费了 5 多分钟，对“InputA”的引用超过 350,000 次。

我尝试了以下宏，它需要立即运行，但不幸的是，它也更改了工作簿中的所有引用，有效地保留了对原始输入表的所有引用。

Sheets("InputA").Name = "temp"
Sheets("InputB").Name = "InputA"
Sheets("temp").Name = "InputB"

有没有办法执行宏但防止对工作表的任何引用更改为新的工作表名称，基本上冻结除了工作表名称更改之外的所有内容？或者也许任何其他可以快速工作的解决方案？我不想遍历 350,000 个实例并使用 INDIRECT() 重写，因为这是我见过的唯一其他解决方案，因为我的引用是复杂的和嵌套的，这需要一段时间。

谢谢。

标签： excelvbaworksheet

Answer 1

假设您的 2 个输入表具有相同的结构，我建议如下：

1. 在工作簿级别创建一个命名范围，将其命名为InputData. 此范围应包含来自 的所有数据InputA。

2. 创建一个帮助表并为其命名Input- 您可以稍后将其设置为隐藏。

3. Input-Data在新工作表中标记与-Range大小完全相同的范围，然后将公式输入=InputData为Array -formula。您可以通过输入Ctrl++来执行此Shift操作Enter。公式应该有大括号，工作表现在应该显示InputA.

4. 更改所有公式以引用辅助工作表Input而不是InputA.

5. 创建一个宏：

   Sub switchInput()
   
       Const sheetAName = "InputA"
       Const sheetBName = "InputB"
   
       With ThisWorkbook.Names("inputData")
           If InStr(.RefersTo, sheetAName) > 0 Then
               .RefersTo = Replace(.RefersTo, sheetAName, sheetBName)
           Else
               .RefersTo = Replace(.RefersTo, sheetBName, sheetAName)
           End If
       End With
   End Sub
   

此例程将仅更改命名范围的定义以指向第一个或第二个输入表。因此，帮助表将显示所选输入表的数据。您所有的公式本身都保持不变。