Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to count worksheets with specific name in Excel?

Have you ever tried to count number of worksheets whose name start with or contain specific word? For example, count the number of worksheets that name start with or contain “KTE”. This article will provide method to help you show the problem.

Count worksheets with specific name with VBA code

Quickly find all worksheets with specific name


Easily list all sheet names of current workbook in Excel:

Using sheet index in your workbook helps you manage your worksheets in ease. With the Create List of Sheet Names utility of Kutools for Excel, you can easily create a sheet index with all sheet names of current workbook listing inside. And you can quickly shift to a certain worksheet by just clicking the sheet name in the Index worksheet. See below screenshot:

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Count worksheets with specific name with VBA code

The following VBA codes help you to count number of worksheets with specific name in a workbook. Please do as follows.


1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module, then copy below VBA code into the Code window.

VBA code 1: Count worksheets whose name start with “KTE”

Sub CountWSNames()
    Dim I As Long
    Dim xCount As Integer
    For I = 1 To ActiveWorkbook.Sheets.Count
        If Mid(Sheets(I).Name, 1, 3) = "KTE" Then xCount = xCount + 1
    Next
    MsgBox "There are " & CStr(xCount) & " sheets that start with 'KTE'", vbOKOnly, "KuTools for Excel"
End Sub

If you want to count number of worksheets contain “KTE”, please apply below VBA code 2.

VBA code 2: Count worksheets whose name contain “KTE”

Sub CountWSNames()
    Dim I As Long
    Dim xCount As Integer
    For I = 1 To ActiveWorkbook.Sheets.Count
        If InStr(1, Sheets(I).Name, "KTE") > 0 Then xCount = xCount + 1
    Next
    MsgBox "There are " & CStr(xCount) & " sheets that with 'KTE'", vbOKOnly, "KuTools for Excel"
End Sub

Note: Please replace the word “KTE” with the one you need count number of worksheets based on.

3. Press the F5 key to run the code. Then a dialog box will pop up to tell you how many worksheets in current workbook start with “KTE” or contain “KTE”. See screenshots:

Number of worksheets whose name start with “KTE”:

Number of worksheets whose name contain “KTE”:


Quickly find all worksheets with specific name.

This section will show you how to quickly find all worksheets with specific name in current workbook with Kutools for Excel.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.

1. After installing Kutools for Excel, the Kutools’ Navigation pane will be opened automatically on the left side of workbook. Otherwise, you can enable the pane by clicking Kutools > Navigation as below screenshot shown.

2. In the Workbook & Sheet section of the navigation pane, check the Filter box and enter the specific worksheet name you with find worksheets based on. Then all worksheets whose name contain the specific word  are filtered and listed in the worksheet list box. See screenshot:

Tip.If you want to have a free trial of this utility, please go to download the software freely first, and then go to apply the operation according above steps.


Related articles:



Recommended Productivity Tools for Excel

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.