Need: Get number of sheets in workbook

DANDERSON · Aug 2nd 2004

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.

jindon · Aug 2nd 2004

Hi,

Easy method:

place the total sheet at the end and in cell

=sum(firstSheetName:LastSheetName!A1)

hope it works

DANDERSON · Aug 2nd 2004

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

jindon · Aug 2nd 2004

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

DANDERSON · Aug 2nd 2004

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:

sh_mar · Aug 26th 2010