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.

  • if the worksheets like

    Sheet1, Sheet2, Sheet3, Sheet4, Sheet5 then
    cell in Sheet5

    Tom, Nancy, Henry, Charles, Total then
    cell in Total

    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!

  • 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


    Welcome to Ozgrid.

    However, please DO NOT resurrect old threads. You posted in a thread that is six years old. Posting your questions in a thread others have started is known as thread hijacking and is against the Forum Rules. Always start your own thread and if helps to clarify your need you can provide a link back to this thread (or any other). This thread is closed. Start your own thread. Thanks.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!