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 <(E-Mail Removed)> 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 <(E-Mail Removed)> 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 <(E-Mail Removed)> 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.