Count how many sheets with a specific name

vzq032372

New Member
Joined
Dec 17, 2015
Messages
14
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:

VBA Code:
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

vzq032372

New Member
Joined
Dec 17, 2015
Messages
14
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
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,380
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

New Member
Joined
Dec 17, 2015
Messages
14
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,380
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

vzq032372

New Member
Joined
Dec 17, 2015
Messages
14
Thank you Fluff.

Now I have to figure out the rest :unsure: 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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,380
Office Version
  1. 365
Platform
  1. Windows
As this is a totally different question, you will need to start a new thread if you need any help.
 

Forum statistics

Threads
1,141,284
Messages
5,705,495
Members
421,398
Latest member
Rahat Anwar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top