How can I loop through a subset of worksheets?

by

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

More Related Contents:

  1. excel vba compare & copy worksheets from 2 excel files
  2. VBA code doesn’t run when cell is changed by a formula
  3. How to add a named sheet at the end of all Excel sheets?
  4. Copy sheet and get resulting sheet object?
  5. Reducing WithEvent declarations and subs with VBA and ActiveX
  6. Excel VBA For Each Worksheet Loop
  7. SumIf using Excel VBA
  8. Test or check if sheet exists
  9. Copy from one workbook and paste into another
  10. How to copy Outlook mail message into excel using VBA or Macros
  11. For Loop not fully cycling in excel VBA [duplicate]
  12. Unique Random Numbers using VBA
  13. Populate unique values into a VBA array from Excel
  14. Excel VBA App stops spontaneously with message “Code execution has been halted”
  15. Closing Excel Application using VBA
  16. How to clear memory to prevent “out of memory error” in excel vba?
  17. Delete Row based on Search Key VBA
  18. How to add a button programmatically in VBA next to some sheet cell data?
  19. What are the benefits of setting objects to “Nothing”
  20. How can I kill task manager processes through VBA code?
  21. VBA: Usage of parentheses for a method
  22. Fully reference a worksheet by codename
  23. OnTime for less than 1 second without becoming Unresponsive
  24. Get Unicode characters with charcode values greater hex `FFFF`
  25. How do I click a link on a web page using Excel VBA?
  26. Ping IP Address with VBA code and return results in Excel
  27. VBA copy cells value and format
  28. Multiply Entire Range By Value?
  29. VBA Excel – IFERROR & VLOOKUP error
  30. Finding a workbook in one of multiple Excel instances

Leave a Comment