vba 对象“_Worksheet”的方法选择失败 - 为什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12697980/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Method Select of Object '_Worksheet' failed - why?
提问by Sam
Set mainWB = Workbooks("Copy Paste.xlsb")
Set mainWS = mainWB.Sheets("Sheet1")
Set testWS = mainWB.Sheets("Sheet3")
mainWS.Select
I keep getting an error on the last line in Excel VBA:
我在 Excel VBA 的最后一行不断收到错误消息：
"Method Select of Object '_Worksheet' failed"
“对象'_Worksheet'的方法选择失败”
Any idea why or how to fix this? Thank you!
知道为什么或如何解决这个问题吗？谢谢！
回答by enderland
As discussed in comments, cannot select Sheetsin VBA that are not active (or Rangeobjects on them).
正如评论中所讨论的，不能Sheets在 VBA 中选择非活动的（或Range它们上的对象）。
For example the following code
例如下面的代码
Sheets(1).Activate
Sheets(2).Range("A1").Select
will cause the error you are receiving.
将导致您收到的错误。
In your case, it seems you are attempting to Selecta sheet which is not active - your mainWSobject is presumably not the ActiveSheetat the point you are calling this code. An easy way to test if this is happening is if you add the following to the end of your code:
在您的情况下，您似乎正在尝试Select使用未激活的工作表 - 您的mainWS对象可能不是ActiveSheet您调用此代码时的对象。测试是否发生这种情况的一种简单方法是将以下内容添加到代码末尾：
if (ActiveSheet.Name <> mainWS.Name) then
msgbox ("Going to crash after clicking ok!")
end if
mainWS.Select
Note that you can refer to the activated worksheet with the command ActiveSheetto either get properties or whatever other operations you are interested in doing.
请注意，您可以使用命令引用激活的工作表以ActiveSheet获取属性或您感兴趣的任何其他操作。
This error can also happen if you have loop working thru all of the worksheets in the workbook and there are hidden sheets. Lookout for that.
如果您通过工作簿中的所有工作表循环工作并且有隐藏的工作表，也会发生此错误。留意那个。
Last, and unrelated to your specific error message, I assume you are declaring those variables somewhere and simply did not copy them here - if not I would consider using Option Explicitas you can often run into all sorts of issues without having Option Explicitat the top of your VBA code.
最后，与您的特定错误消息无关，我假设您在某处声明了这些变量，只是没有将它们复制到此处 - 如果不是，我会考虑使用，Option Explicit因为您经常会遇到各种问题而无需Option Explicit在 VBA 的顶部代码。
回答by jufemaiz
While I agree with the above, it is also important to note that the Delete function will not work if the worksheet's visibility is currently set to xlSheetVeryHidden
虽然我同意上述观点，但同样重要的是要注意，如果工作表的可见性当前设置为 xlSheetVeryHidden，则删除功能将不起作用
回答by Mark
I had the same issue and looked at this post for ideas on how to fix it. My issue was resolved by using "Activate" as opposed to "Select" on the line that my code was failing on. So instead of using "mainWS.Select", try using "mainWS.Activate" instead.
我遇到了同样的问题，并查看了这篇文章以获取有关如何解决它的想法。我的问题是通过在我的代码失败的那一行上使用“激活”而不是“选择”来解决的。因此，不要使用“mainWS.Select”，而是尝试使用“mainWS.Activate”。
