Sum sheets based on criteria

[npop03]

Hello, I have a workbook containing 30+ worksheets. For simplicity, let's say
I have 30 sheets with data and a sheet with formulas that displays totals.
The setup and columns are all the same for the 30 sheets. The names of the
sheets start at 1 and go to 30.

In the total sheet, I would like to be able to type in a range, say 1 to 7,
and have the totals from only those sheets calculated. The totals will be the
sum of the whole column. So, it would be the sum total of column E for sheets
1 through 7.

I am thinking about entering the range and then clicking a command button
that would do the work. Any ideas?

 

[Don Guillett]

If?? you have a total in each sheet in the SAME cell then
=sum(sheet1:sheet7!a3)

 

[npop03]

Correct, and that's what I am doing for the total of all sheets, but I am
wondering if there's a way to type "sheet 1" in a cell, "sheet 7" in another
cell and then have your formula below change accordingly.

I am making this for non-experienced Excel users and I'd like to make it so
they don't have to change the formula each time (otherwise your solution is
what I would do)

Let me know if this helps clarify things and if it's even possible, thanks!

 

[Don Guillett]

No can do, even with indirect. You will have to have the sheets in order by
index.

Sub sumacrossshts()
fs = Sheets(CStr(Range("f3"))).Index
ls = Sheets(CStr(Range("f4"))).Index
For i = fs To ls
'MsgBox Sheets(i).Name
ms = ms + Sheets(i).Range("a3")
Next i
MsgBox ms
End Sub

 

[npop03]

Don, I think your solution will work - I was wondering if we could modify 2
things:

1) Instead of a message box, I would like to place the value in a cell - say
J4...

2) Instead of summing only one cell for each sheet, is there a way we can
sum the entire column for each sheet? Specifically, instead of summing A3, I
would like to sum column K.

Please let me know and thanks for your patience and help!

 

[Don Guillett]

Assumes you have a total in the same cell on each sheet, ie:a3
to put in a cell on the active sheet
'MsgBox ms
range("a3").value=ms