How to Count Worksheets in a Workbook and its name

In this post, we will learn how to know how many worksheets are there in the workbook and those names.

Many of you have faced this problem sometimes, that you want to know how many sheets are there in the workbook. Few of them are hidden or very hidden as well. It is difficult to know such files as they are hidden/Very hidden. 

 

1. Using Name Manager

 

Step1.  Go to Name Manager (Press Ctrl+F3 then Click on New) or

Go to Formulas > Name Manager. See below snapshot:

Step2. Click on New

Step3. Provide a name e.g. (Sheets) & Put this formula in Refers to "=GET.WORKBOOK(1)&T(NOW())" or "=GET.WORKBOOK(1)&T(TODAY())"

Step4.  Put this formula in a cell wherever you want to get the output "=COUNTA(INDEX(Sheets,0))" , see below snapshot:

 

2. Using default function (Sheets)

Just put the function anywhere in the sheet "=SHEETS()"

See below snapshot:

Please note: This function is available in 2013 or later version

3. Using VBA

 

Step1. Open VB Editor by pressing Alt+F11

Step2. Copy below mentioned coding and paste there

Sub SheetsCount()
    'Below 2 lines will let us know count of Total Sheets in the workbook
    n = Worksheets.Count
    Range("A1").Value = "Total Sheets --> " & n
    'Below coding will let us know all the sheets names
    For i = 1 To n
        Range("A" & i + 1).Value = Sheets(i).Name
    Next i
End Sub

-----------------------------------------------------------------------------------------------------------------------------------------------

Download the file : SheetsCount.xlsm

Thanks for learning, we will come soon with new post.

Please give you valuable feedback/comments, if it's helpful.