PDA

View Full Version : Solved: Count Sheets With Specific Name



zoom38
05-24-2007, 06:30 AM
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
05-24-2007, 06:36 AM
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
05-24-2007, 07:03 AM
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
05-24-2007, 07:06 AM
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
05-24-2007, 07:10 AM
Outstanding MV works perfectly.

Thank You
Gary