views:

1746

answers:

4

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.

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

Tried:

Set ThisWorkSheet = Sheets("FlagNew")

and

For Each Sheets("FlagNew") In Worksheets

Ideas?

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
+1  A: 

How about?

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

This code might not be quite right. I'm writing it in the SO editor not VBA, but you get the idea.

Ade Miller
I should have mentioned I thought of that, but it's not efficient enough. There could be hundreds of worksheets in front of the FlagStart worksheet. I would really rather start right at the correct worksheet. Thanks though!
Kirk Hings
@Kirk Hings - premature optimization will bite you! I'd suggest getting a working solution first, then look at ways to optimize it.Also I would be worried having hundreds of sheets. Is Excel the best tool?
Christian Payne
good points, i actually am optimizing the solution that they are already using. currently they copy paste a bunch of this stuff and we are trying to automate it for them. i guess i exaggerated at hundreds, but there can be dozens of sheets, and unfortunately they vary in number and sheet names. i'm trying these solutions right now. i hit on using the index yesterday after posting this problem.
Kirk Hings
+2  A: 

I believe that if you use "foreach" you won't have any control over the starting sheet. For that matter, I am not even sure you are guaranteed the order in which the iteration will take place.
I think what you should do is first, get the index of the sheet you are interested in (get the sheet by name, and get its index), and then iterate using a for loop, over the indexes of the sheets starting at the flag sheet index.
[Edit: I hacked through a quick example]

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
Mathias
You are correct. Worksheets are a collection of sheet. You don't know where it is.
Christian Payne
A: 

Do the sheets you iterate over have a common name format?

Ex)

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"

If so, in your for each loop, just do a Left(ThisWorkSheet.name, 4) = "emp" to verify if it's a sheet you want to reference.

JakeTheSnake
A: 

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.

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
Kirk Hings
(solution edited into original question above with code formatting)
Kirk Hings