Advice needed - counting tabs

[gjmink]

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!!

 

[Ken Wright]

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

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

 

[Sandy Mann]

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

 

[Ken Wright]

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.

 

[Sandy Mann]

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