仅在活动工作表而不是整本书上运行 Excel VBA 脚本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22052823/
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
Run an Excel VBA script on only the active worksheet and not the entire book?
提问by InfiniteGeoff
I am using a VBA script to essentially find/replace. Right now when I run the VBA script it applies to all open sheets in the workbook. I wish for the VBA script to only apply in the active sheet and not touch the rest.
我正在使用 VBA 脚本来查找/替换。现在，当我运行 VBA 脚本时，它适用于工作簿中所有打开的工作表。我希望 VBA 脚本仅适用于活动工作表而不触及其余部分。
Here is my current macro code:
这是我当前的宏代码：
Sub ReplaceCC()
'
' ReplaceCC Macro
' Add CC to Distributor, Reseller, Government and Retail.
'
'
Range("A1").Select
Cells.Replace What:="Distributor", Replacement:="DistributorCC", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Reseller", Replacement:="ResellerCC", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Government", Replacement:="GovernmentCC", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Retail", Replacement:="RetailCC", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
采纳答案by InfiniteGeoff
It seems the macro was not recognizing the active sheet properly since the sheets were copies of each other. If you copy the content on Sheet 1 and create a new sheet then paste the content on it this will work. Each sheet needed to be unique and not just a copy of the original.
由于工作表是彼此的副本，因此宏似乎无法正确识别活动工作表。如果您复制工作表 1 上的内容并创建一个新工作表，然后将内容粘贴到其上，这将起作用。每张纸都必须是独一无二的，而不仅仅是原件的副本。
回答by Albibi
I got the same problem. It depends of the Find/Replace history: if in the previous manualuse of Find/Replace, you specified "Options > Search in Workbook", your macro will apply to all sheets. Otherwise, with the default "In Sheet", the macro will stay in the original Worksheet. I still don't know how to fix that from within the macro, though.
我遇到了同样的问题。这取决于查找/替换历史记录：如果在之前手动使用查找/替换时，您指定了“选项 > 在工作簿中搜索”，则您的宏将应用于所有工作表。否则，使用默认的“在工作表中”，宏将保留在原始工作表中。不过，我仍然不知道如何从宏中解决这个问题。
回答by user4350993
This is due to a VBA bug. When you use "Find" in VBA, the parameter "LookAt:=xlPart" or "LookAt:=xlWhole" works as expected. But when you use "Replace", the coded parameter is ignored and the replace uses the last MANUAL setting for its scope. The workaround is to do a "Find" operation right before the "Replace" operation, since "Find" uses the coded "LookAt" scope (thereby making the "Find"'s scope the last setting used, so the "Replace" will then use the scope coded into the "Find").
这是由于 VBA 错误造成的。在 VBA 中使用“查找”时，参数“LookAt:=xlPart”或“LookAt:=xlWhole”按预期工作。但是当您使用“替换”时，编码参数将被忽略，并且替换使用其范围的最后一个 MANUAL 设置。解决方法是在“替换”操作之前执行“查找”操作，因为“查找”使用编码的“LookAt”范围（从而使“查找”的范围成为最后使用的设置，因此“替换”将然后使用编码到“查找”中的范围）。
回答by william hartanto
Solution from http://www.vbaexpress.com/forum/showthread.php?11444-Solved-Replace-in-a-sheet-and-not-the-entire-workbook
来自http://www.vbaexpress.com/forum/showthread.php?11444-Solved-Replace-in-a-sheet-and-not-the-entire-workbook 的解决方案
Dim dummy As Range
Set dummy = Worksheets(1).Range("A1:A1").Find("Dummy", LookIn:=xlValues)
Then you can post REPLACE functions underneath these lines of codes
然后你可以在这些代码行下面发布 REPLACE 函数
回答by MattB
Interesting question. I believe that you could do what you're trying to do by simply placing "ActiveSheet" before each object reference in your code.
有趣的问题。我相信您可以通过在代码中的每个对象引用之前简单地放置“ActiveSheet”来完成您想要做的事情。
For example: Instead of Range("A1").Selectuse ActiveSheet.Range("A1").select
例如：代替Range("A1").Select使用ActiveSheet.Range("A1").select
If you set up your code like this, whenever you execute, it should carry out its actions on the worksheet you currently have active.
如果您像这样设置代码，则无论何时执行，它都应该在您当前处于活动状态的工作表上执行其操作。
EDIT: You should check to see which module you have this code placed in. If it is in an inserted module, I think you should be fine. On the other hand, if you've inserted it in one of the sheet modules it will only modify the sheet it is attached to. If you insert ActiveSheetbefore your cell references and the rest it will still modify the active sheet instead of the sheet the code is attached to.
编辑：您应该检查一下您将此代码放置在哪个模块中。如果它在插入的模块中，我认为您应该没问题。另一方面，如果您已将其插入到一个工作表模块中，它只会修改它所附加到的工作表。如果ActiveSheet在单元格引用之前插入，其余部分仍然会修改活动工作表而不是代码附加到的工作表。
