Worksheets in Excel

[New Judge]

Does anybody know a way to count the number of worksheets in an Excel workbook by means of a function or a bit of simple VB code?

A need to count the number of worksheets provided in a weekly return and use that number in my analysis.

[Bushwhacker]

First you will have to define this reference...

From the drop-down-menu Insert > Name > Define

In the �Names in Workbook� box type in the word Sheets

Then Copy & Paste this

=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")

into the �Refers to� box :

Click OK

Then in a blank cell anywhere in any worksheet enter this formula.

=COUNTA(Sheets)

That should do the trick for you.

Cheers,

BW

[kempie]

Ths VBA module...

Function NumSheets()
NumSheets = Worksheets.Count
End Function


...will return the number of worksheets in the workbook by the use of =NumSheets() in a cell.

[New Judge]

Thanks both.

I've used kempie's method as it seems slightly more straightforward. Works a treat!