Prev: Reverse / Mirror Image of Text
Next: How to do - B2 is only valid if B1 is zero
From: Mike on 13 Apr 2010 13:08
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)+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" <Suresh(a)discussions.microsoft.com> wrote in message
news:CB4170CF-8228-42C4-BE69-2F2BF9AC9E5C(a)microsoft.com...

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

Re: 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/aspnet/f843db77-a775-415e-bd08-71c2b1127e40/biztalk-writing-and-usin.aspx
From: Don Guillett on 13 Apr 2010 15:46
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
dguillett(a)gmail.com
"Mike Anklesaria" wrote in message
news:201041313821manklesaria(a)optimum-insurance.com...
> 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)+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" <Suresh(a)discussions.microsoft.com> wrote in message
> news:CB4170CF-8228-42C4-BE69-2F2BF9AC9E5C(a)microsoft.com...
>
> On Friday, February 15, 2008 3:03 AM
> Herbert Seidenberg wrote:
>
> Re: 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/aspnet/f843db77-a775-415e-bd08-71c2b1127e40/biztalk-writing-and-usin.aspx

 | 
Pages: 1
Prev: Reverse / Mirror Image of Text
Next: How to do - B2 is only valid if B1 is zero