HOW TO CALCULATE TOTAL NUMBER OF WORKSHEETS IN A FILE

[VDS]

@ Dear All,

I have a an excel file as per attachment. No of work sheets may increase as per requirement. Here, I want to calculate total no of work sheets.

Could you please help ?

VDS

 

[BBD]

Hi VDS,

Insert the below code in the VBA module of the file.

Public Function CountSheet()
Application.Volatile
CountSheet = ThisWorkbook.Sheets.Count
End Function

Once you insert it you can use it as a formula in any cell - say A1 =Countsheet()

Cheers,
BD

 

[VDS]

Hai BD,

Thanks for your reply. The previous attachment provided is incorrect. The correct data is attached. Here, about 190 worksheets are coming in a file. This function applies only to a single worksheet. How can I apply this for all the worksheets ?


VDS

 

[BBD]

Hi VDS,

I originally though that you will be pasting the code in the workbook where you will use the functionn and not in the Personal workbook. The issue in having it in personal workbook is that if you send a file to someone, it wont work.

If you still want to have it in personal workbook, change the code to this.

Public Function CountSheet()
 Application.Volatile
 CountSheet = ActiveWorkbook.Sheets.Count
 End Function

This should do the trick.

Cheers,
BD

 

[VDS]

This is done. Thanks you so much

VDS

 

[BBD]

Glad to help

 

[DBExcel]

A solution for those who prefer not to use VBA. (I love VBA, but you don't want it in all files)

Add the command to the ribbon to 'View Document Properties'

In that dialog box under contents, it will name all worksheets including hidden worksheets, manually count up the results.

If you need this count dynamically for a VBA solution, stick with BBD's answer, otherwise this is a quick code-free method.

 

[vrunda]

How & from where to add to ribbon 'View Document Properties'?

 

[DBExcel]

See attached:

in 2010 version,

File > Options > Quick Access Toolbar > Choose Commands From: ALL COMMANDS > View Document Properties > Add >>

 

[BBD]

Hi DBExcel,

I dont see it. By the look of it, it looks like a custom created options.

 

[DBExcel]

Make sure you look in the all commands listing - it would be at the bottom. To clarify I use Office 2010 Pro, not sure of other versions. See attachment in previous posting

 

[Ronald Wilson]

Try just "Properties" and look at the Contents tab. I am using Excel 2010.

 

[SirJB7]

Hi, VDS!

There's a formula only solution that has certain constraints but maybe it helps here. The INFO function.

The INFO function used with the argument "NUMFILE" retrieves the number of active worksheets, but for all the opened workbooks in the actual Excel running instance, that's to say, it includes all worksheets from your add-ins and other workbooks.

There're 2 workarounds:

a) If you always have the same workbooks opened, and just vary the new one for which you want to know how many worksheets does it has, do this:
Once.
- open a new Excel instance with the usual workbooks
- create a new file
- in A1 of 1st sheet type this:
=INFO("numfile")
- keep the displayed value minus the no. of worksheets of the new file (usually 3) as the "other worksheets"
Every time you open a new workbook.
- place the same INFO function in any empty cell
- subtract the "other worksheets" number from the retrieved value, that'd be the no. of worksheets of the opened file.

b) If you constantly vary the number of workbooks, do this:
- place the info formula in any empty cell of a yet opened workbook
- open the new workbook
- place the info formula in any empty cell of the new workbook
- subtract both values, that'd be the no. of worksheets of the opened file.

Hope it helps. But if you ask me for an advice, use the UDF at the start of this thread.

Regards!

 

[Debraj]

Just another Macro 4.0 based solution..

• Define a Name
  
  • Name : NoOfSheet
  • RefersTo : =GET.WORKBOOK(1)&T(NOW())
• Now use a formula as..
  • =COUNTA(INDEX(NoOfSheet,0))

Caution.. It reqquires Refresh (F9), whenever No of sheets update, to reflect the same..

or a 3D Reference Solution.. if all sheets A1 is filled with some data then..

=COUNTA(Sheet1:Sheet11!A1)

where Sheet1 is the starting Sheet's Name and Sheet11 is the last Sheet's Name..

 

[vrunda]

Try just "Properties" and look at the Contents tab. I am using Excel 2010.

Hello Ronald!!
Got it - by properties --Advanced properties --contents tab. Name of worksheets are visible. No.of ws not shown.

 

[vrunda]

DBExcel You r Right.
IN Customize ribbon we get it - All commands- view document properties.
Just as same in Properties tab in above post.

 

[ThrottleWorks]

@SirJB7 , Sir thanks for "Info function", I was not aware of this.

This is as interesting as Get.Cell function, will be useful in my work.

P.S. - Hope this will not be considered as hijacking the post. :D

 

[SirJB7]

Hi, ThrottleWorks!

You're welcome, and if wanna hijack go ahead, hijack all you want... but first send me a down payment for the ransom, Pounds, Euros or US Dollars only, four digits or greater. Got my account no?

About INFO function, take care that with "Osversion" parameter in my case it retrieves:
Windows (32-bit) NT 6.01
which is wrong since my Windows is x64 and my Office is x86.

Regards!

 

[ThrottleWorks]

Sure Sir, I will keep this in mind.