Solved: Count Sheets With Specific Name

[zoom38]

Anyone know how to count the number of worksheets that start with "Playoff". I have multiple sheets named "Playoff 1", Playoff 2", etc. The number of Playoff sheets vary from season to season so I am trying to count the number of sheets that begin with Playoff.

Thanks
Gary

[mvidas]

Hi Gary,

You can loop through the sheets, like:

Sub zoom38CountWSNames()
 Dim WS As Worksheet, iCnt As Long
 For Each WS In ActiveWorkbook.Worksheets
  If InStr(1, WS.Name, "Playoff", vbTextCompare) = 1 Then iCnt = iCnt + 1
 Next
 MsgBox "There are " & CStr(iCnt) & " sheets that start with 'playoff'"
End Sub

Matt

[zoom38]

Very nice MV but I do have a problem. I have sheets named "Playoff 1", "Playoff 2" etc. but I also have a sheet named "Playoff Stats" that is also being counted. Is there a way to just count the other sheets outside of the obvious by subtracting 1?

Thank
Gary

[mvidas]

So you want it to be Playoff followed by a number? Sure, no problem, though this time I'm going to use Like instead of InStr:

Sub zoom38CountWSNames()
 Dim WS As Worksheet, iCnt As Long
 For Each WS In ActiveWorkbook.Worksheets
  If LCase(WS.Name) Like "playoff #*" Then iCnt = iCnt + 1
 Next
 MsgBox "There are " & CStr(iCnt) & " sheets that start with 'playoff'"
End Sub

Matt

[zoom38]

Outstanding MV works perfectly.

Thank You
Gary