excelvbasumtabs

How to count number of tabs containing a word - VBA

I'm trying to figure out how to check which tabs contain a certain word and to count these instances.

I have it set up currently that when a "client" is lost, their data tab is renamed to contain "ARCHIVED". I'm trying to do a count to see how many clients have been lost, so was ideally hoping I'd be able to do a sum of the tabs containing "ARCHIVED" but I'm uncertain how to go about this.

Solution

  • Loop through the sheets and validate their names using InStr.

    Sub Demo()
    Dim Sht As Object
    Dim iCount As Long
    For Each Sht In ThisWorkbook.Sheets
        If InStr(1, Sht.Name, "ARCHIVED", vbTextCompare) > 0 Then
            iCount = iCount + 1
        End If
    Next
    MsgBox "Found " & iCount & " sheets"
End Sub