This worked great the last time I had a question, so I'd thought we to would
try again. Is there a function available that would count the number of tabs
in a spreadsheet that has many many tabs. Thanks for your help!!
--
Have a great day and a better tomorrow!

Put it in a module and call it with =NumShts()

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"gjmink" wrote in message
...
This worked great the last time I had a question, so I'd thought we to would
try again. Is there a function available that would count the number of tabs
in a spreadsheet that has many many tabs. Thanks for your help!!
--
Have a great day and a better tomorrow!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.802 / Virus Database: 545 - Release Date: 26/11/2004

Ken,

(as you know), it won't recalculate unless you add an

Application.Volatile

line. The odd thing is that although it doesn't recalculate without the
added line on a sheet recalculate or when a sheet is added, it does
when a sheet is deleted ( in XL97.)

Is it the same in other versions and do you know why that would be?

Regards

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"Ken Wright" wrote in message
...
Put it in a module and call it with =NumShts()

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--



"gjmink" wrote in message
...
This worked great the last time I had a question, so I'd thought we to
would
try again. Is there a function available that would count the number of
tabs
in a spreadsheet that has many many tabs. Thanks for your help!!
--
Have a great day and a better tomorrow!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.802 / Virus Database: 545 - Release Date: 26/11/2004

Hi Sandy - my bad, should have included it, and should probably also have used
ThisWorkbook as opposed to ActiveWorkbook. Works the same in all versions up to
2003 I believe (or seems to), and no I have no idea why it fires for one and not
the other. When you say 'a sheet is added' though, I assume you mean via
'Insert New Worksheet', because obviously you can add sheets by copying or
moving etc, and when done via this route it does trigger it.

Either way though, just as easy to get round that particular issue by using the
Workbook_NewSheet in the TW module to trigger a calculation.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------


<snip


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.803 / Virus Database: 546 - Release Date: 30/11/2004

Thank you Ken,

the other. When you say 'a sheet is added' though, I assume you mean via
'Insert New Worksheet', because obviously you can add sheets by copying or
moving etc, and when done via this route it does trigger it.

curiouser and curiouser

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"Ken Wright" wrote in message
...
Hi Sandy - my bad, should have included it, and should probably also have
used
ThisWorkbook as opposed to ActiveWorkbook. Works the same in all versions
up to
2003 I believe (or seems to), and no I have no idea why it fires for one
and not
the other. When you say 'a sheet is added' though, I assume you mean via
'Insert New Worksheet', because obviously you can add sheets by copying or
moving etc, and when done via this route it does trigger it.

Either way though, just as easy to get round that particular issue by
using the
Workbook_NewSheet in the TW module to trigger a calculation.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--


<snip


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.803 / Virus Database: 546 - Release Date: 30/11/2004