vba 循环遍历工作簿中的每个工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26597389/
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
Loop to go through each worksheet in workbook
提问by Kaylin Zeuske
I'm trying to loop through each worksheet in the workbook but it's not working. I think it's the ActiveWorkbook.Worksheets not recognizing each worksheet and only running on the Active worksheet. Any suggestions would be great! I'm sure this is a basic fix but can't seem to figure it out. Thanks!
我正在尝试遍历工作簿中的每个工作表，但它不起作用。我认为这是 ActiveWorkbook.Worksheets 无法识别每个工作表而仅在活动工作表上运行。任何建议都会很棒！我确定这是一个基本的修复，但似乎无法弄清楚。谢谢！
Sub LoopThroughWorksheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If (ws.Name <> "Sheet1") And (ws.Name <> "Sheet2") And (ws.Name <> "Sheet8") And (ws.Name <> "Sheet42") Then
With ws
'code
'Sheets("Formula").Select
Range("FormulaRow").Copy
'Selection.Copy
ActiveSheet.Select
ActiveSheet.Range("A1").Select
ActiveSheet.Paste
Calculate
Range("Q1:X1").Select
Selection.Copy
Range("Q3:X3000").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Calculate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
End If
Next ws
End Sub
回答by John Bustos
For a Withstatement, you need to proceed each time you wish to reference that object with a .to use it the way you're intending.
But, if I understand your intnent, I think the easiest solution would be to add in this line of code:
对于With语句，每次您希望使用 a 引用该对象时都需要继续进行，.以便按照您想要的方式使用它。但是，如果我理解您的意图，我认为最简单的解决方案是添加以下代码行：
ws.Select
as follows:
如下：
For Each ws In ActiveWorkbook.Worksheets
If (ws.Name <> "Sheet1") And (ws.Name <> "Sheet2") And (ws.Name <> "Sheet8") And (ws.Name <> "Sheet42") Then
ws.Select
With ws
'code
'Sheets("Formula").Select
Range("FormulaRow").Copy
...
and then you should also work on changing things such as from Range("FormulaRow").Copyto .Range("FormulaRow").Copyso you're sure to be using the Withstatement correctly.
然后您还应该努力更改诸如 from 之类的内容Range("FormulaRow").Copy，.Range("FormulaRow").Copy以便您确保With正确使用该语句。
Hope that makes sense...
希望这是有道理的...
