 # Need: Get number of sheets in workbook

• I wish to have a workbook, that gets updated regularly with new data. I wish to ceate a totals sheet that has the ability to take data from a particular cell, in every sheet in the workbook.

All sheets are formated the same.

i.e. I want to add up all [A:1] cells in every sheet.
trouble is, the number of sheets grows on a daily basis. How do I formulate a variable to determined the number of sheets first? then add up the current amount of data in eash sheet? Is there a better approach?

Thanks in advance. I tried to do a search, but I am not exactly sure what syntax I should be looing for.

I am new to VB, so please explain in easy terminology.

• Hi,

Easy method:

place the total sheet at the end and in cell

=sum(firstSheetName:LastSheetName!A1)

hope it works

• Is firstSheetName and LastSheetName variables I need to get first? Thats what I am having trouble defining as well.

• if the worksheets like

Sheet1, Sheet2, Sheet3, Sheet4, Sheet5 then
cell in Sheet5
=sum(Sheet1:Sheet4!A1)

Tom, Nancy, Henry, Charles, Total then
cell in Total
=sum(Tom:Charles!A1)

if you add new sheet in between the firstsheet and last sheet
i.e. Sheet1 and Sheet4 or Tom and Charles
it will be automatically included

• Thanks for the help!

I ended up creating a sheet "0" and a sheet "9999z99"
then I hide the two new sheets so they would not interfere with the user, and then ran the =SUM('0:9999z99'!A\$1).

I will be able to apply this, and the hide sheet for many applications I need.

I appreciate the help again!
:thanx:

• Re: Need: Get number of sheets in workbook

Hi
i used the Sheet1:Sheet3!A but it returns a 0 (Zero), would you know why ?

• Re: Need: Get number of sheets in workbook

Hi Again,
i have created 3 and 7 sheets but it still brings back a 0. where do you place the formula ? does it matter where it is on the page ?

• Re: Need: Get number of sheets in workbook

The formula does not return the number of sheets on its own. It adds up (sums) the values in the A1 cells for all sheets included in the formula. If you wish to return the number of sheets then you need to put a 1 in the A1 cell of each sheet.

The formula can go anywhere on a sheet and on any sheet.

• Re: Need: Get number of sheets in workbook

sh_mar,

Welcome to Ozgrid.