ExcelBanter - How do I count the number of worksheets used in a file?

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - How do I count the number of worksheets used in a file? (https://www.excelbanter.com/excel-discussion-misc-queries/176675-how-do-i-count-number-worksheets-used-file.html)

How do I count the number of worksheets used in a file?

I have a number of workbooks with a large number of worsksheets filled out
(not by me) in each workbook. Does anyone know how to count the number of
worksheets used other than actually manually counting by clicking each
worksheet?

How do I count the number of worksheets used in a file?

You would need to use some VBA code -- you can't do it with just formulas.
For example,

Sub AAA()
Dim WB As Workbook
Dim NumSheets As Long
For Each WB In Application.Workbooks
NumSheets = WB.Worksheets.Count
Debug.Print "Workbook: " & WB.Name & _
" has " & CStr(NumSheets) & " worksheets"
Next WB
End Sub

This code requires that the workbooks be open. You might want to post more
details about what you want to accompish.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

"Suresh" wrote in message
...
I have a number of workbooks with a large number of worsksheets filled out
(not by me) in each workbook. Does anyone know how to count the number of
worksheets used other than actually manually counting by clicking each
worksheet?

How do I count the number of worksheets used in a file?

=COUNTA('*'!A1)+1
assuming A1 in each sheet is non-blank.
Kudos: Lori

# of workshhets in a file

Hi

I am trying to use =COUNTA('*'!A1) to calculate the number of non blank sheets I have in a workbook. It works well, however I need this to calculate for many cells and everytime someone adds a worksheet I have to re-enter the calculation as the * is replaced with the actual values each time. Is there anyway to change this or is there another solution?

Herbert Seidenberg wrote:

How do I count the number of worksheets used in a file?
15-Feb-08

=COUNTA('*'!A1)+1
assuming A1 in each sheet is non-blank.
Kudos: Lori

Previous Posts In This Thread:

On Thursday, February 14, 2008 5:56 AM
Sures wrote:

How do I count the number of worksheets used in a file?
I have a number of workbooks with a large number of worsksheets filled out
(not by me) in each workbook. Does anyone know how to count the number of
worksheets used other than actually manually counting by clicking each
worksheet?

On Thursday, February 14, 2008 6:02 AM
Chip Pearson wrote:

You would need to use some VBA code -- you can't do it with just formulas.
You would need to use some VBA code -- you can't do it with just formulas.
For example,

Sub AAA()
Dim WB As Workbook
Dim NumSheets As Long
For Each WB In Application.Workbooks
NumSheets = WB.Worksheets.Count
Debug.Print "Workbook: " & WB.Name & _
" has " & CStr(NumSheets) & " worksheets"
Next WB
End Sub

This code requires that the workbooks be open. You might want to post more
details about what you want to accompish.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

"Suresh" wrote in message
...

On Friday, February 15, 2008 3:03 AM
Herbert Seidenberg wrote:

How do I count the number of worksheets used in a file?
=COUNTA('*'!A1)+1
assuming A1 in each sheet is non-blank.
Kudos: Lori

Submitted via EggHeadCafe - Software Developer Portal of Choice
BizTalk: Writing and using a custom referenced functoid.
http://www.eggheadcafe.com/tutorials...-and-usin.aspx

# of workshhets in a file