Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count the number of worksheets in a workbook
Hello:
Is there any function or other method to count the number of worksheets in a workbook? I have a workbook which includes dozens of worksheets (each for one client) and I would like to know the total number of clients. Thanks in advance! |
#2
|
|||
|
|||
with VBA
Function WSNum() WSNum = Activeworkbook.Worksheets.Count End Function -- HTH RP (remove nothere from the email address if mailing direct) "Vincdc" wrote in message ... Hello: Is there any function or other method to count the number of worksheets in a workbook? I have a workbook which includes dozens of worksheets (each for one client) and I would like to know the total number of clients. Thanks in advance! |
#3
|
|||
|
|||
On Mon, 17 Jan 2005 09:07:03 -0800, Vincdc
wrote: Hello: Is there any function or other method to count the number of worksheets in a workbook? I have a workbook which includes dozens of worksheets (each for one client) and I would like to know the total number of clients. Thanks in advance! A VBA UDF (User Defined Function) would be best. It is possible to use a Named formula GET.WORKBOOK(4) but this can cause problems resulting in Excel crashing under certain circumstances. If you are only counting Worksheets, as you write, then: ============================ Function SheetCount() SheetCount = ActiveWorkbook.Worksheets.Count End Function ============================ However, be aware the Charts are not Worksheets. So if you have Chart sheets that you wish to include in the count, use: ========================= Function SheetCount() SheetCount = ActiveWorkbook.Sheets.Count End Function ======================= To enter this UDF, <alt-F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer Window, then Insert/Module and paste the code into the window that opens. In some cell in the workbook, enter the formula =SheetCount() The cell should display the count. --ron |
#4
|
|||
|
|||
Thanks!
Just be curious, is there any built-in formula or function for this one? I mean not the VBA code. "Bob Phillips" wrote: with VBA Function WSNum() WSNum = Activeworkbook.Worksheets.Count End Function -- HTH RP (remove nothere from the email address if mailing direct) "Vincdc" wrote in message ... Hello: Is there any function or other method to count the number of worksheets in a workbook? I have a workbook which includes dozens of worksheets (each for one client) and I would like to know the total number of clients. Thanks in advance! |
#5
|
|||
|
|||
There is no built-in function to do this. You must use VBA.
-- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Vincdc" wrote in message ... Thanks! Just be curious, is there any built-in formula or function for this one? I mean not the VBA code. "Bob Phillips" wrote: with VBA Function WSNum() WSNum = Activeworkbook.Worksheets.Count End Function -- HTH RP (remove nothere from the email address if mailing direct) "Vincdc" wrote in message ... Hello: Is there any function or other method to count the number of worksheets in a workbook? I have a workbook which includes dozens of worksheets (each for one client) and I would like to know the total number of clients. Thanks in advance! |
#6
|
|||
|
|||
You could create a workbook name, say NumSheets, with a refers to value of
=GET.WORKBOOK(4) , and then use =NumSheets in the worksheet. But be warned, if you copy this cell in any Excel version prior to XL2002, it causes an Excel crash. so I advise against it. -- HTH RP (remove nothere from the email address if mailing direct) "Vincdc" wrote in message ... Thanks! Just be curious, is there any built-in formula or function for this one? I mean not the VBA code. "Bob Phillips" wrote: with VBA Function WSNum() WSNum = Activeworkbook.Worksheets.Count End Function -- HTH RP (remove nothere from the email address if mailing direct) "Vincdc" wrote in message ... Hello: Is there any function or other method to count the number of worksheets in a workbook? I have a workbook which includes dozens of worksheets (each for one client) and I would like to know the total number of clients. Thanks in advance! |
#7
|
|||
|
|||
Really thanks for all your help! :=)
"Ron Rosenfeld" wrote: On Mon, 17 Jan 2005 09:07:03 -0800, Vincdc wrote: Hello: Is there any function or other method to count the number of worksheets in a workbook? I have a workbook which includes dozens of worksheets (each for one client) and I would like to know the total number of clients. Thanks in advance! A VBA UDF (User Defined Function) would be best. It is possible to use a Named formula GET.WORKBOOK(4) but this can cause problems resulting in Excel crashing under certain circumstances. If you are only counting Worksheets, as you write, then: ============================ Function SheetCount() SheetCount = ActiveWorkbook.Worksheets.Count End Function ============================ However, be aware the Charts are not Worksheets. So if you have Chart sheets that you wish to include in the count, use: ========================= Function SheetCount() SheetCount = ActiveWorkbook.Sheets.Count End Function ======================= To enter this UDF, <alt-F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer Window, then Insert/Module and paste the code into the window that opens. In some cell in the workbook, enter the formula =SheetCount() The cell should display the count. --ron |
#8
|
|||
|
|||
I'm not sure if it's important, but this version will calculate the number of
worksheets in the workbook with the formula. Option Explicit Function WSNum() WSNum = Application.Caller.Parent.Parent.Worksheets.Count End Function Application.caller is the cell containing the formula. its parent is the worksheet. the worksheet's parent is the workbook. If you open two workbooks (each with a different number of worksheets in them) and use window|arrange|horizontal. You should be able to notice the difference. Vincdc wrote: Hello: Is there any function or other method to count the number of worksheets in a workbook? I have a workbook which includes dozens of worksheets (each for one client) and I would like to know the total number of clients. Thanks in advance! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Slow opening Excel Workbook with over 50 Worksheets | Excel Discussion (Misc queries) | |||
How do I create repeating worksheets in one workbook and have it . | Excel Discussion (Misc queries) | |||
Count number of shaded cells | Excel Discussion (Misc queries) | |||
count formula between worksheets | Excel Discussion (Misc queries) | |||
Removing links to other worksheets from within a workbook | Excel Discussion (Misc queries) |