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
"Mike K" wrote:
> Hi Mike,
> I put the code there and when I select the "Control" sheet I
> get a Compile error: Expected End Sub
>
> Mike
>
> "Mike" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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
|
Similar Threads
