How can I determine the number of worksheets?

Question

Is there any way, with VBA, to determine the number of worksheets in a workbook and perform calculations on a predefined range of cells in those worksheets?

Worksheets are regularly added to the workbook and we need to calculate the sum of a specific range of cells.

I googled it but did not find any solution.

Answer 1

Loop Through Worksheets With Exceptions List

New Post (03/30/2021)

• Let's say I've learned a thing or two since this answer was posted. Since it was one of my best-received ones, I decided to add an improvement (correction) addressing the following issues:
  • introducing a range to make the solution more OP(user)-friendly: ... we need to calculate the sum of a specific range of cells...,
  • qualifying the worksheets by creating a reference to the workbook: Dim wb... and wb.Worksheets vs Worksheets,
  • getting rid of Hungarian notation: Exceptions vs vntExceptions,
  • declaring the array as a string: () As String vs As Variant,
  • and getting rid of the inner loop by using Application.Match vs For j = 0... Next j.
• I'll be leaving the old post since it is working (inferiorly) and is educational in handling For loops, using Exit For, and checking the counter if it is greater than the upper limit of the array to determine if a value has been found in the array.

• Performs operations in the 'Code in here' section on all worksheets in the workbook containing this code, except the ones in the Exception List.

At Least One Exception

Option Explicit

Sub loopThroughWorksheets()
        
    Const ExceptionsList As String = "Sheet1,Sheet2"
    Const RangeAddress As String = "A1:E5"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim Exceptions() As String: Exceptions = Split(ExceptionsList, ",")
    
    Dim ws As Worksheet
    
    For Each ws In wb.Worksheets
        If IsError(Application.Match(ws.Name, Exceptions, 0)) Then
            ' Code in here e.g.
            Debug.Print ws.Name, Application.Sum(ws.Range(RangeAddress))
        
        End If
    Next ws
    
End Sub

If you'll allow the Exceptions List to be empty ("") use the following code:

No Exceptions Is Allowed

Sub loopThroughWorksheets()
        
    Const ExceptionsList As String = "Sheet1,Sheet2"
    Const RangeAddress As String = "A1:E5"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim Exceptions() As String: Exceptions = Split(ExceptionsList, ",")
    
    Dim wsNames() As String: ReDim wsNames(1 To wb.Worksheets.Count)
    Dim ws As Worksheet
    Dim n As Long
    
    If UBound(Exceptions) = -1 Then ' no exception: 'ExceptionList = ""'
        For Each ws In wb.Worksheets
            n = n + 1
            wsNames(n) = ws.Name
        Next ws
    Else
        For Each ws In wb.Worksheets
            If IsError(Application.Match(ws.Name, Exceptions, 0)) Then
                n = n + 1
                wsNames(n) = ws.Name
            End If
        Next ws
        ReDim Preserve wsNames(1 To n)
    End If
    
    For n = 1 To n
        Set ws = wb.Worksheets(wsNames(n))
      ' Code in here e.g.
        Debug.Print ws.Name, Application.Sum(ws.Range(RangeAddress))
    
    Next n
    
End Sub

Old Post (12/30/2018)

• Performs operations in the 'Code in here' section on all worksheets except the ones in the Exception Comma-Separated List.
• The difference between the two versions is that the first version uses the object control variable ws, but the second version doesn't need it, but uses the control variable i and the .Count property of the Worksheets collection.
• If you don't have any exceptions i.e. you want to perform operations on all worksheets, then just leave cExceptions as "".

The For Each Next Approach

Sub WorksheetsForEach()

  ' Exceptions Comma-Separated List
  Const cExceptions As String = "Sheet1,Sheet2"
  
  Dim ws As Worksheet           ' Current Worksheet
  Dim vntExceptions As Variant  ' Exceptions Array
  Dim j As Integer              ' Exceptions Counter
    
  vntExceptions = Split(cExceptions, ",")
  
  For Each ws In Worksheets
    With ws
      For j = 0 To UBound(vntExceptions)
        If .Name = vntExceptions(j) Then
          Exit For
        End If
      Next
      If j > UBound(vntExceptions) Then
      ' Code in here e.g.
        Debug.Print .Name
      
      End If
    End With
  Next

End Sub

The For Next Approach

Sub WorksheetsForNext()

  ' Exceptions Comma-Separated List
  Const cExceptions As String = "Sheet1,Sheet2"
  
  Dim vntExceptions As Variant  ' Exceptions Array
  Dim i As Integer              ' Worksheets Counter
  Dim j As Integer              ' Exceptions Counter
    
  vntExceptions = Split(cExceptions, ",")
  
  For i = 1 To Worksheets.Count
    With Worksheets(i)
      For j = 0 To UBound(vntExceptions)
        If .Name = vntExceptions(j) Then
          Exit For
        End If
      Next
      If j > UBound(vntExceptions) Then
      ' Code in here e.g.
        Debug.Print .Name
      
      End If
    End With
  Next

End Sub