vba 循环浏览 Excel 表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20422356/
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 through Excel Sheets
提问by adp
I have the following Code and I would like it to run in 25 other sheets of the Workbook and instead of repeating the code 25 times,for each sheet is there a way to make it loop?
我有以下代码，我希望它在工作簿的其他 25 张工作表中运行，而不是将代码重复 25 次，对于每张工作表，有没有办法让它循环？
Can someone assist?
有人可以帮忙吗？
Sub DeleteEmptyRows()
Dim ws As Worksheet
Dim strSearch As String
Dim lRow As Long
strSearch = "ressort"
Set ws = Sheets("01,02,03")
With ws
lRow = .Range("A" & .Rows.Count).End(xlUp).Row
With .Range("A1:A" & lRow)
.AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
ActiveSheet.Range("$A:$P536").AutoFilter Field:=1
End With
End Sub
回答by
Wrap the processing code in a loop
将处理代码包装在一个循环中
for each ws in thisworkbook.sheets
' do something on each worksheet
next
example
例子
Sub DeleteEmptyRows()
Dim ws As Worksheet
Dim strSearch As String
Dim lRow As Long
strSearch = "ressort"
For Each ws In ThisWorkbook.Sheets
If (ws.Name <> "Sheet1") And (ws.Name <> "Sheet2") And (ws.Name <> "Sheet3") Then
With ws
lRow = .Range("A" & .Rows.Count).End(xlUp).Row
With .Range("A1:A" & lRow)
.AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
ws.Range("$A:$P536").AutoFilter Field:=1
End With
End If
Next
End Sub
so now if the sheet names are Sheet1 or Sheet2 or Sheet3 they will be skipped.
所以现在如果工作表名称是 Sheet1 或 Sheet2 或 Sheet3，它们将被跳过。
回答by RobinL
Your code will need to be stored in a module, rather than being contained in a sheet. The following illusrates how the loop works:
您的代码需要存储在模块中，而不是包含在工作表中。下面说明了循环的工作原理：
Sub test()
Dim thisSheet As Worksheet
For Each sheet In Sheets
thisSheet.Cells(1, 1) = 1
Next
End Sub
