如何循环遍历一组工作表？ [英] How can I loop through a subset of worksheets?

问题描述

我知道如何循环使用工作簿中的所有工作表，以及如何在达到结束标记工作表时退出：

I know how to loop through all the worksheets in a workbook, and how to exit once I reach an 'end-flag' worksheet:

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

但是我不能让循环开始于'标志工作表（甚至在起始标志工作表之后的工作表上更好），例如，标记的开始/结束工作表位于一堆其他工作表的中间，因此开始或结束遍历是不可行的。

However I cannot get the loop to begin on a 'start-flag' worksheet (or even better on the worksheet right after the start-flag worksheet. For example the flagged start/end worksheets are in the middle of a bunch of other worksheets, so beginning or end traversing is not workable.

在FlagStart表之前可能有数百个工作表，所以我真的需要从正确的表单开始。

There could be hundreds of worksheets before that 'FlagStart' sheet, so I really need to start on the right sheet.

尝试：

Set ThisWorkSheet = Sheets("FlagNew")

和

For Each Sheets("FlagNew") In Worksheets

想法？

解决方案：
Mathias非常接近，但是，与自定义结束索引相比，我们实际上想出了我的最终解决方案，但是想要给予信用，这是我的最终解决方案：

Solution: Mathias was very close, but dendarii was that tiny step closer with the custom ending index. I actually figured out my final solution on my own, but wanted to give credit. Here was my final solution:

Private Sub CommandButtonLoopThruFlaggedSheets_Click()
    ' determine current bounds
    Dim StartIndex, EndIndex, LoopIndex As Integer
    StartIndex = Sheets("FlagNew").Index + 1
    EndIndex = Sheets("FlagEnd").Index - 1

    For LoopIndex = StartIndex To EndIndex
        MsgBox "this worksheet is: " & Sheets(LoopIndex).Name
        ' code here
    Next LoopIndex
End Sub

推荐答案

如果这不是一个特别可变的工作簿（即工作表不被添加和删除），您可以将工作表的名称存储在一个隐藏的范围内

If this is not a particularly changeable workbook (i.e. worksheets are not being added and deleted all the time), you could store the names of the worksheets in a range on a hidden sheet and loop through them by name.

然而，这听起来像是在工作簿中连续存储，所以建立在Mathias的解决方案上，你可以使用函数返回开始和结束工作表的索引，然后循环：

However, it sounds like they are stored consecutively in the workbook so, building on Mathias' solution, you could use a function to return the indices of the start and end worksheets and then loop through:

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

这篇关于如何循环遍历一组工作表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！