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


On Wed, 12 Dec 2007 16:26:00 -0800, Jo wrote:

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.

Thanks Gord Dibben, but I'm not sure if this is a function or something
available in the menus....more information please

"Gord Dibben" wrote:

Sub sheets_num()
MsgBox ActiveWorkbook.Sheets.Count & " sheets in workbook"
End Sub


Gord Dibben MS Excel MVP


On Wed, 12 Dec 2007 16:26:00 -0800, Jo wrote:

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.

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/ExcelArticle...functions.html

"Mike Anas" wrote:

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/ExcelArticle...functions.html

Many thanks but it's sounding a bit beyond my capabilities. I'll just count the sheets!

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/ExcelArticle...functions.html

Many thanks but it's sounding a bit beyond my capabilities. I'll just
count the sheets!

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)" wrote:

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/ExcelArticle...functions.html

Many thanks but it's sounding a bit beyond my capabilities. I'll just
count the sheets!

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

"Herbert Seidenberg" wrote:

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.

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

On Thu, 13 Dec 2007 14:43:02 -0800, Jo wrote:



"Herbert Seidenberg" wrote:

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.

Aah, excellent! Good value.

=COUNTA('*'!A1)+1

assuming A1 in each sheet is non-blank.

"Jo" wrote:

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.

"Herbert Seidenberg" wrote:

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.

Your formula is great except it doesn't update automatically.

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

Thanks.



"Rick Rothstein (MVP - VB)" wrote:

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/ExcelArticle...functions.html

Many thanks but it's sounding a bit beyond my capabilities. I'll just
count the sheets!

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

Here's another method...

Create this defined formula
Goto the menu InsertNameDefine
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))

--
Biff
Microsoft Excel MVP


"paul" wrote in message
...
Your formula is great except it doesn't update automatically.

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

Thanks.



"Rick Rothstein (MVP - VB)" wrote:

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/ExcelArticle...functions.html

Many thanks but it's sounding a bit beyond my capabilities. I'll just
count the sheets!

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