VBA 代码从特定工作表开始循环工作表（索引 3）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24846069/
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
VBA Code to cycle through worksheets starting with a specific sheet (index 3)
提问by user3720702
I need to cycle through sheets of index 3 tip last sheet and run a code. I tried something like this but its doesn't work.
我需要遍历索引 3 的最后一张纸并运行代码。我试过这样的事情，但它不起作用。
If (ws.sheetIndex > 2) Then
With ws
'code goes here
End With
End If
I did a search but don't find a solution to this problem. Help would be much appreciated.
我进行了搜索，但没有找到解决此问题的方法。帮助将不胜感激。
I also tried:
我也试过：
Dim i As Long, lr As Long
Dim ws As Worksheet
Windows("Book1").Activate
With ActiveWorkbook
Set ws = .Worksheets("index")
For i = 3 To 10
'code goes here
Next i
End With
回答by djikay
You can try the following, which iterates over all worksheets in your workbook and only "acts" for worksheets with index 3 or above.
您可以尝试以下操作，它会遍历工作簿中的所有工作表，并且仅对索引为 3 或更高的工作表进行“操作”。
Dim sheet As Worksheet
For Each sheet In ActiveWorkbook.Worksheets
If sheet.Index > 2 Then
' Do your thing with each "sheet" object, e.g.:
sheet.Cells(1, 1).Value = "hi"
End If
Next
Note that this doesn't put a hard limit on the number of sheets you have (10 or whatever), as it will work with any number of worksheets in your active workbook.
请注意，这不会对您拥有的工作表数量（10 或其他）设置硬性限制，因为它可以处理活动工作簿中任意数量的工作表。
EDIT
编辑
If you want the code to run on worksheets with names "Sheet" + i(where iis an index number from 3 onwards), then the following should help:
如果您希望代码在带有名称的工作表上运行"Sheet" + i（其中i是从 3 开始的索引号），那么以下内容应该会有所帮助：
Dim sheet As Worksheet
Dim i As Long
For i = 3 To ActiveWorkbook.Worksheets.Count
Set sheet = ActiveWorkbook.Worksheets(i)
If sheet.Name = "Sheet" & i Then
' Do your thing with each "sheet" object, e.g.:
sheet.Cells(2, 2).Value = "hi"
End If
Next i
Of course, this means that the names of your worksheets need to always follow this pattern, so it's not best practice. However, if you're sure the names are going to stay like this, then it should work well for you.
当然，这意味着您的工作表的名称需要始终遵循此模式，因此这不是最佳实践。但是，如果您确定名称将保持这样，那么它应该适合您。
回答by Pieter Geerkens
Note that the user can reorder the sheets in the Worksheets Collection, so it is better to refer to the sheets by CodeName (which the user cannot change), and exclude by CodeName the sheets to be skipped, as here:
请注意，用户可以对 Worksheets Collection 中的工作表重新排序，因此最好通过 CodeName（用户无法更改）引用工作表，并通过 CodeName 排除要跳过的工作表，如下所示：
Public Sub TestLoop()
On Error GoTo ErrHandler
Dim ws As Worksheet, s As String
For Each ws In Worksheets
If ws.CodeName <> "Sheet2" Then
s = s & vbNewLine & ws.CodeName
End If
Next ws
s = "WorksheetList (except Sheet2:" & vbNewLine & vbNewLine & s
MsgBox s, vbOKOnly, "Test"
EndSUb:
Exit Sub
ErrHandler:
Resume EndSUb
End Sub
If I drag Sheet 3 to precede Sheet1, the MsgBox outputs:
如果我将 Sheet 3 拖到 Sheet1 之前，MsgBox 会输出：
WorksheetList (except Sheet2:
Sheet3
Sheet1
回答by osquro
Try excluding first and second sheet using name:
尝试使用名称排除第一张和第二张纸：
Public Sub Sheets3andUp()
Dim ws As Worksheet
Dim nameOfSheet1 As String
Dim nameOfSheet2 As String
nameOfSheet1 = "Sheet1"
nameOfSheet2 = "Sheet2"
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> nameOfSheet1 And ws.Name <> nameOfSheet2 Then
'Code goes here
Debug.Print ws.Name
End If
Next ws
End Sub
结束子
