在不知道工作表名称的情况下在Excel中查找和激活命名范围 [英] Find and activate named range in Excel without knowing Worksheet name

问题描述

我正在编写一个宏，它将更新一堆工作簿.

I'm writing a macro that will update a bunch of workbooks.

假定工作簿已经打开.

我需要激活(或选择一个)具有名称(命名范围)的单元格，但最重要的是我不知道它所在的工作表.

I need to activate (or select, either one) a cell that has a name (named range) but the kicker is that I don't know what worksheet it is in.

换句话说:我保证:

• 命名的范围存在.
• 命名范围对于打开的工作簿是全局唯一的.

我不能保证:

• 命名范围将在同一工作表索引中.
• 在工作簿之间，工作表的名称相同.

任何建议都会受到赞赏，即使是向我展示了如何从全局命名范围返回工作表的建议也是如此，因为这足以让我激活工作表并从那里开始.但是理想情况下，该解决方案将向我展示如何激活/选择/更新(最终结果是我更新该单元格，或者如果有帮助，则在下面插入行)一个命名范围单元格，而无需知道工作表名称.

Any advice will be appreciated, even one that shows me how to back into a worksheet from a global named range since that will be enough for me to then activate the worksheet and go from there. But ideally the solution will show me how to activate/select/update (the final result would be that I update the cell, or insert rows below if that helps) a named range cell without knowing the worksheet name.

我正在Excel 2007中执行此操作

I am doing this in Excel 2007

谢谢！

推荐答案

假设您已将范围命名为"test" .

Suppose you have named range "test".

方法1:

Dim rng As Range
Set rng = Range("test")
'or if you have more than one workbook opened
'Set rng = ThisWorkbook.Names("test").RefersToRange
'select sheet
rng.Parent.Select
'select named range
rng.Select

方法2:

'for currently active workbook
Application.Goto "test"

这篇关于在不知道工作表名称的情况下在Excel中查找和激活命名范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！