Puzzle ?

hi Franz

one possible solution would be to place blank 'dummy' sheets as the beginning and ending sheets in your workbook - you could call them 'first' and 'last' or something along those lines - then you could have a formula like this:

=SUM(first:last!N4)

which would sum the values in cell N4 in all sheets located between the 'first' sheet and the 'last' sheet, regardless of how many sheets there are or what you name them. would this work for you?

hth
kevin

This would probably work fairly well as long as the user does not play around with the sheet tabs.I would need a macro to insert a blank sheet called "end" as the last worksheet in the series and at the same time place the SUM formula in a cell on the first worksheet which will be called "Estimate". Can you help?Thanks.

hi franz

i came up with a different idea using vba so that you would not have to insert any blank sheets:


in this code, 'lastwks' equals the total number of worksheets in the workbook,
'firstsheet' is the name of the first worksheet, and last sheet is the name of the last worksheet. then this code creates a formula in cell A1 of the active sheet that totals the amounts in cell N4 of all your sheets. using this, you do not have to insert any dummy sheets at the beginning or end of your worksheet tabs, and it will continue to work as you add more sheets to your workbook, regardless of what sheet names you use, or in what order you put them. modify this code to suit you own needs. would this work better for you?

hth
kevin

Kevin,

This is a most excellent solution and takes out the "user" error factor. Thank you for getting back to me with this code.

Kind Regards

Kevin,

Hmmm, I've been thinking and was wondering what is the syntax to have the "SumAllSheets" code disabled when the user creates a copy of the worksheet that contains say a command button for the macro.

This way I can have the code only work on one main worksheet.Currently I have the user create a copy of the worksheet in question by pressing a command button. Thanks in advance.

if you want the code to only work in one particular sheet, then modify your code as follows:


(EDITED CODE)

where "Sheet1" is the name of the sheet you want this code to work in - it will not work in any of the other sheets. modify to suit your needs

hth
kevin
This message was edited by kskinne on 2002-11-19 16:17