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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.