transfer total from one sheet to another

[Guest]

I have a workbook with about 10 sheets. In each sheet there are total in j2,
h2, and L2 (same in each sheet). I want these totals to appear in a total
sheet; say with name of sheet then the three totals. I can seem to do this.
pls help.

 

[Ragdyer]

One way would be to list your sheet names down a column.
If they're the default XL names, say you enter:
Sheet1
in A1.
Then, simply drag down to copy and automatically increment the names.

Enter this formula in B1:
=INDIRECT(A1&"!H2")

Enter this formula in C1:
=INDIRECT(A1&"!J2")

Enter this formula in D1:
=INDIRECT(A1&"!L2")

NOW, select *all 3*, B1, C1, and D1,

And drag down the 3 cell selection to copy as needed.

 

[Don Guillett]

if you wanted the sum from all intervening sheets
=sum(firstsheet:lastsheet!j2)

but if you want the sheet name too
Sub eachwsi()
For i = 1 To Worksheets.Count
with sheets(i)
If.Name <> "Total" Then
Cells(i, "c") =.Name
Cells(i, "d") =.Range("j2")
Cells(i, "e") =.Range("h2")
Cells(i, "f") = .Range("l2")
End If
end with
Next i