如何循环访问工作表的子集?

如果这不是一个特别可更改的工作簿(即工作表不会一直被添加和删除),则可以将工作表的名称存储在隐藏工作表的某个范围内,并按名称循环。

但是,听起来它们是连续存储在工作簿中的,因此,根据Mathias的解决方案,可以使用函数返回开始和结束工作表的索引,然后循环:

Public Function GetStartIndex() As Integer
    On Error Resume Next
    GetStartIndex = ThisWorkbook.Worksheets("MyStartingWorksheet").Index + 1
End Function

Public Function GetEndIndex() As Integer
    On Error Resume Next
    GetEndIndex = ThisWorkbook.Worksheets("MyEndingWorksheet").Index - 1
End Function

Sub LoopThrough()

    Dim wks As Worksheet
    Dim i As Integer
    Dim iStart As Integer
    Dim iEnd As Integer

    iStart = GetStartIndex()
    iEnd = GetEndIndex()

    If iStart > 0 And iEnd > 0 And iEnd > iStart Then
        For i = iStart To iEnd
            Set wks = ThisWorkbook.Worksheets(i)
            MsgBox wks.Name
        Next i
    End If

End Sub

我相信如果你使用“foreach”,你将无法控制起跑记录。对于这一点,我甚至不确定您是否能保证迭代的发生顺序。
我认为您应该做的是,首先获取您感兴趣的工作表的索引(按名称获取工作表,并获取其索引),然后使用for循环对从标志工作表索引开始的工作表索引进行迭代。
[编辑:我通过一个简单的例子]

Sub Iterate()

Dim book As Workbook
Dim flagIndex As Integer
Dim flagSheet As Worksheet

Set book = ActiveWorkbook
Set flagSheet = book.Worksheets("Sheet3")
flagIndex = flagSheet.Index

Dim sheetIndex As Integer
Dim currentSheet As Worksheet

For sheetIndex = flagIndex To book.Worksheets.Count
    Set currentSheet = book.Worksheets(sheetIndex)
Next

End Sub

怎么样?

For Each ThisWorkSheet In Worksheets  
    If ThisWorkSheet.Name = "FlagStart" Then output = true 
    If ThisWorkSheet.Name = "FlagEnd" Then Exit For   
    If output = true Then MsgBox "This worksheet name is: " & ThisWorkSheet.Name
Next

这段代码可能不太正确。我把它写在so编辑器中,而不是vba,但你明白了。

您迭代的工作表是否具有通用的名称格式?

前)

Sheets(0).name > "Reports"
Sheets(1).name > "Start Here"
Sheets(2).name > "emp.0001"
Sheets(3).name > "emp.0002"
Sheets(4).name > "emp.0003"
Sheets(5).name > "emp.0004"
Sheets(6).name > "End Here"

如果是,在for each循环中,只需执行 Left(ThisWorkSheet.name, 4) = "emp" 以验证它是否是要引用的工作表。

在Excel vba 2013中,如果您有要在选项卡“blankfirst”和“blanklast”之间更新的工作表,那么这是有效的。

使用下面的代码来测试它将返回选项卡名称,然后替换您的操作代码 MsgBox wks.Name 部分。

Sub Macro2()

    On Error Resume Next
    GetStartIndex = ThisWorkbook.Worksheets("Blankfirst").Index + 1

    On Error Resume Next
    GetEndIndex = ThisWorkbook.Worksheets("Blanklast").Index - 1

    Dim wks As Worksheet
    Dim i As Integer
    Dim iStart As Integer
    Dim iEnd As Integer

    iStart = GetStartIndex
    iEnd = GetEndIndex


    If iStart > 0 And iEnd > 0 And iEnd > iStart Then
        For i = iStart To iEnd
            Set wks = ThisWorkbook.Worksheets(i)
            MsgBox wks.Name
        Next i
    End If

End Sub

 Public Sub ITERATE_WORKSHEETS()  
   On Error Resume Next    
   Dim x As Long  
         For x = 0 To 100  
              MsgBox Worksheets(x).Name  
         Next x   
   On Error GoTo 0  
   MsgBox "all done"  
 End Sub