count worksheets

  • Thread starter Thread starter Jo
  • Start date Start date
J

Jo

can you tell how many worksheets are in your workbook without physically
counting them? I have renamed my sheets so they're not consecutively
numbered any more.
 
Sub sheets_num()
MsgBox ActiveWorkbook.Sheets.Count & " sheets in workbook"
End Sub


Gord Dibben MS Excel MVP
 
Thanks Gord Dibben, but I'm not sure if this is a function or something
available in the menus....more information please
 
Mike Anas said:
Gord is showing you a VBA subroutine that will do the trick. I'm not
aware of a built in Excel function that will answer this question for
you without the use of VBA. The following link might be helpful to
you, if you want to learn about creating user-defined functions, which
is what appears to be necessary.

http://www.vertex42.com/ExcelArticles/user-defined-functions.html

Many thanks but it's sounding a bit beyond my capabilities. I'll just count the sheets!
Click to expand...
 
Gord is showing you a VBA subroutine that will do the trick. I'm not
Many thanks but it's sounding a bit beyond my capabilities. I'll just
count the sheets!
Click to expand...

Try the following... From any worksheet, press Alt+F11; this will take you
to the VBA editor. Click Insert/Module on the VBA editor's menubar. A code
window will popup that has the title "Book 1 - Module1 (Code)"... copy the
following three lines of code (note that it is slightly different than what
Gord posted) and paste them into that code window

Function CountSheets()
CountSheets = ActiveWorkbook.Sheets.Count
End Function

Now, go back to the worksheet (you can close the VBA editor if you want; the
code you just copied into it will be saved when you save the workbook); type
this into any cell...

=CountSheets()

and press Enter. You should see a count of the number of sheets in your
workbook. You can use CountSheets() inside any formulas you create (in this
workbook) just like it was a regular built-in spreadsheet function.

Rick
 
Or without code or formulas:
Right-click on any tab > Select All Sheets
Select a cell location that you don't normally use,
say HZ10.
Type in it an odd sequence of letters, say QXYZ.
QXYZ will now appear on all sheets at HZ10
Edit > Find > QXYZ > Find All
The number of sheets will appear in the
lower left corner of the Find window.
A format color instead of QZYZ also works.
This way you can select A1 and not accidentally
overwrite data.
 
Rick Rothstein (MVP - VB) said:
Try the following... From any worksheet, press Alt+F11; this will take you
to the VBA editor. Click Insert/Module on the VBA editor's menubar. A code
window will popup that has the title "Book 1 - Module1 (Code)"... copy the
following three lines of code (note that it is slightly different than what
Gord posted) and paste them into that code window

Function CountSheets()
CountSheets = ActiveWorkbook.Sheets.Count
End Function

Now, go back to the worksheet (you can close the VBA editor if you want; the
code you just copied into it will be saved when you save the workbook); type
this into any cell...

=CountSheets()

and press Enter. You should see a count of the number of sheets in your
workbook. You can use CountSheets() inside any formulas you create (in this
workbook) just like it was a regular built-in spreadsheet function.

Rick

Thanks Rick - that's what I needed - I can do that
Click to expand...
 
Herbert Seidenberg said:
Or without code or formulas:
Right-click on any tab > Select All Sheets
Select a cell location that you don't normally use,
say HZ10.
Type in it an odd sequence of letters, say QXYZ.
QXYZ will now appear on all sheets at HZ10
Edit > Find > QXYZ > Find All
The number of sheets will appear in the
lower left corner of the Find window.
A format color instead of QZYZ also works.
This way you can select A1 and not accidentally
overwrite data.
Click to expand...
Aah, excellent! Good value.
 
Herbert

A clever solution.

Just one caveat,,,,,,,,,,,,only worksheets will be counted so if user has chart
sheets they will be missed.


Gord
 
Herbert Seidenberg said:
Or without code or formulas:
Right-click on any tab > Select All Sheets
Select a cell location that you don't normally use,
say HZ10.
Type in it an odd sequence of letters, say QXYZ.
QXYZ will now appear on all sheets at HZ10
Edit > Find > QXYZ > Find All
The number of sheets will appear in the
lower left corner of the Find window.
A format color instead of QZYZ also works.
This way you can select A1 and not accidentally
overwrite data.
Click to expand...
 
Your formula is great except it doesn't update automatically.

How do you make it update without manually re-entering it each time?

Thanks.
 
Here's another method...

Create this defined formula
Goto the menu Insert>Name>Define
Name: SheetNames
Refers to:

=GET.WORKBOOK(1)&T(NOW())

OK out

Then to count how many sheets are in a file:

=COUNTA(INDEX(SheetNames,0))
 
Back
Top