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

"Mike Anklesaria" wrote in message
...
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