Count how many sheets with a specific name

[vzq032372]

I have the following code to count the number of tabs that contain a specific word - eventually I want to create a column for each of the tabs and collect some data from it but I'm stuck right now on this:

Sub count_shts()

Dim i As Integer
Dim sht As Worksheet
Dim shtName As String

If sht.Name Like "Summary*" Then 'THIS IS WHERE THE ERROR IS OCCURRING

For i = 0 To ActiveWorkbook.Worksheets.Count

Next i

MsgBox i

End If


End Sub

 

[vzq032372]

I found my answer below - - will post another question to add the columns based on the sheet name

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

 

[Fluff]

How about

Sub vzq()
   Dim i As Long
   Dim ws As Worksheet
   
   For Each ws In Worksheets
      If ws.Name Like "Summary*" Then i = i + 1
   Next ws
   MsgBox i
End Sub

 

[vzq032372]

That's not working because I have 9 sheets and the result is returning 2.

I would actually like to
1) count the tabs that contain the word "Estimate"
2) create columns with those tab names as the header

and then I have to add a bunch of formulas to refer to that tab or just copy the 2021 column to the Main Summary tab

 

[Fluff]

I was just going by the code you originally posted, as you gave no details.

 

[vzq032372]

Thank you Fluff.

Now I have to figure out the rest how to create columns on the master using the tab names....

the reason I used the count sub routine is to determine how many columns to add. If you can help, here's the scenario:
1) Code copies select tabs from multiple workbooks and adds it to the "Master" - this works as intended, however,
2) on the "Summary Cover" sheet of the "Master" workbook, I need to add a column for each tab which will summarize the data that's in there,
alternatively, I can copy of the "Total" column from each of those tabs and paste them to the Summary Cover, but it will still need the tab name in the header.

The tab name is the workbook's name that it came from which will be different each time.

 

[Fluff]

As this is a totally different question, you will need to start a new thread if you need any help.