Need formula

[sandy]

Hello!

I need a formula for counting the number of worksheets in
a workbook.

Any help will be greatly appreciated.

Sandy

 

[J.E. McGimpsey]

One way:

Paste this User Defined Function (UDF) in a regular code module
(ALT-F11 then Insert/Module):

Public Function NumWorksheets() As Double
NumWorksheets = _
Application.Caller.Parent.Parent.Worksheets.Count
End Function

Call from XL as

=NumWorksheets()

 

[David McRitchie]

Hi Sandy,
I'm reasonably sure you would need VBA code. You could
create your own User Defined Function.

Option Explicit
Function worksheets_count() As Long
worksheets_count = Worksheets.Count
End Function

usage:
=Worksheets_Count()
=personal.xls!worksheets_count()
="Number of worksheets: " & worksheets_count()

To install a macro or a function see my getstarted.htm web page.

 

[Andrew Jones]

This works for me:

=INFO("numfile")

a lot simpler!

 

[Sandy]

Thanks you so much!

I also need a formula for counting the sheets between
SheetSomething through SheetSomethingElse inclusive in a
workbook containing X number of sheets.

This should be easy, but help doesn't give any good
examples.

Sandy

 

[J.E. McGimpsey]

A lot simpler, but since it counts "active sheets", it also counts
sheets in ALL open workbooks and add-ins, it doesn't meet the OP's
criterion of "number of worksheets in *a* workbook" (emphasis added).

Interesting try, though! INFO() almost never crosses my mind...

 

[David McRitchie]

Thanks for the explanation, I could see =INFO("numfile") gave the wrong answer.