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

  • Thread starter Thread starter Suresh
  • Start date Start date
S

Suresh

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?
 
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)
 
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:

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

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

Previous Posts In This Thread:

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?

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 Workboo
Dim NumSheets As Lon
For Each WB In Application.Workbook
NumSheets = WB.Worksheets.Coun
Debug.Print "Workbook: " & WB.Name &
" has " & CStr(NumSheets) & " worksheets
Next W
End Su

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

--
Cordially
Chip Pearso
Microsoft Most Valuable Professiona
Excel Product Group, 1998 - 200
Pearson Software Consulting, LL
www.cpearson.co
(email on web site


Re: How do I count the number of worksheets used in a file?
=COUNTA('*'!A1)+
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/tutorial...08-71c2b1127e40/biztalk-writing-and-usin.aspx
 
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
in message
news:[email protected]...
 
Back
Top