How to count number of tabs containing a word - VBA

Question

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.

Please share the details of the code you tried and describe the issue you are encountering. — taller
– taller, Commented Feb 26 at 17:47
@Aimee Allow me a remark: Stack Overflow has the option to upvote helpful answers and if there is an answer that you feel answers your question (best) you can accept it by ticking the green checkmark on its left. The sites tour tells you a bit more about that. "Someone answers" — T.M.
– T.M., Commented Feb 27 at 18:07

taller · Accepted Answer · 2025-02-26 17:51:20Z

1

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

answered Feb 26 at 17:51

taller

19.7k2 gold badges9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BigBen Feb 26 at 17:55

My reading from OP is that "data tab" would refer to a worksheet specifically, while Sheets would include chart sheets. But who uses those nowadays :-)

Aimee Feb 26 at 17:58

Thanks this was perfect! I needed the result to populate in a table so I omitted the MsgBox and popped the range.value in there instead. Thanks :)

taller Feb 26 at 20:01

@BigBen Maybe you are right. But who knows.

Collectives™ on Stack Overflow

How to count number of tabs containing a word - VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related