Count worksheets begining with certain text

[Mike K]

Oh Wise Ones,
I did some searching and found some code that
counts worksheets with a particular name. I need something similar but I
need to place the count in a cell on a sheet named "Control". How would I
count the number of sheets that start with AC and place the number in cell A1
and count the number of sheets that start with NM and place them in cell A2.
As sheets are added the numbers would increment. The last part of the code
adds worksheets which I don't want to do programatically, but looks like it
would preclude code to insert the count to a cell, so I left it Please
advise.

Many Thanks,
Mike

Private Sub worksheetMaker()
Dim WB As Workbook
Dim SH As Worksheet
Dim i As Long
Dim iCtr As Long
Const sName As String = "Report"

For i = 1 To ThisWorkbook.Worksheets.Count
If Worksheets(i).Name Like sName & "*" Then
iCtr = iCtr + 1
End If
Next i
'
'If iCtr = 0 Then iCtr = 1
Set SH = Worksheets.Add(after:=Worksheets(Worksheets.Count))
SH.Name = sName & iCtr + 1
End Sub

 

[Mike]

If I have understood correctly the code below will do what you want. Just one
point, you didn't say which sheet was to contain the data so I have assumed a
sheet called Index. If your workbook doesn't have one then create one or
change the code to another sheet.

Sub countem()
Const sName1 As String = "MM"
Const sName2 As String = "AC"

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name Like sName1 & "*" Then
mm = mm + 1
ElseIf ws.Name Like sName2 & "*" Then
ac = ac + 1
End If
Next ws
Worksheets("Index").Cells(1, 1).Value = mm & " Sheets beging with MM"
Worksheets("Index").Cells(2, 1).Value = ac & " Sheets beging with AC"
End Sub


Will that do?
Mike.

 

[Mike K]

Many Thanks Mike! I put it in thisworkbook and it returns the correct count
when run. How would I force it to increment as soon as a new sheet is added
without manually running the sub?

Mike

 

[Mike]

I was looking at that but unfortunately adding a sheet isn't a workbook event
that could fire a macro. In any case when you add a sheet it will have a
default sheet name (Sheet99) so wouldn't add to the count anyway.

The best place to put it is right click the tab on your sheet named control
and paste it into the 'worksheet_Activate event.

Mike

 

[Mike K]

Hi Mike,
I put the code there and when I select the "Control" sheet I
get a Compile error: Expected End Sub

Mike

 

[Mike K]

My bad! I left the Sub countem() and End Sub from the copy paste. Works great!

Thanks Mike and have a great day

 

[Mike]

That's en error in copying. The code should exactly like this.

Private Sub Worksheet_Activate()
Const sName1 As String = "MM"
Const sName2 As String = "AC"
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name Like sName1 & "*" Then
mm = mm + 1
ElseIf ws.Name Like sName2 & "*" Then
ac = ac + 1
End If
Next ws
Worksheets("Control").Cells(1, 1).Value = mm & " Sheets begin with MM"
Worksheets("Control").Cells(2, 1).Value = ac & " Sheets begin with AC"
End Sub