add cell across all worksheets

M

Marcia

Hi! How do I add the sum of E21 across all but one worksheet? The
worksheet name has been changed to the first day of the week--053005, etc.
E21 contains the hours worked in one week.

I want to calculate totally yearly hours and then do some calculations off
that number.

Any help would be greatly appreciated. I'm very familiar with vba in
Access, but I've done minimal in Excel. Thanks.

Marcia
 
B

Bob Phillips

Marcia,

Move that sheet to the start and then use

=SUM('first real sheet name:last sheet name'!E21)

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
M

Marcia

Thanks for the reply. here is what I have thus far. Reads the data, but...
because E21 is actually of the Time type, it reads a strange decimal number.
How can I make it read as time? thanks.

Marcia

Private Sub CommandButton1_Click()

Dim wsht As Worksheet
Dim myshts As Range
Dim allowshts As Sheets
Dim mycel As Range
Set allowshts = Worksheets
Dim mytothours As Integer

For Each wsht In allowshts
Set myshts = wsht.Range("e21")
For Each mycel In myshts
If mycel.Value > 0 Then
mytothours = mytothours + mycel.Value
End If
Next mycel
Next wsht

End Sub
 
B

Bob Phillips

Time is just held as a fraction of a day, so if you want to see it as time,
format the result cell as [hh]:mm, or multiply by 24 to see as a decimal
number.
 
M

Marcia

Thanks so much. That solved it.

Marcia

Bob Phillips said:
Time is just held as a fraction of a day, so if you want to see it as
time,
format the result cell as [hh]:mm, or multiply by 24 to see as a decimal
number.

--
HTH

Bob Phillips

Marcia said:
Thanks for the reply. here is what I have thus far. Reads the data, but...
because E21 is actually of the Time type, it reads a strange decimal number.
How can I make it read as time? thanks.

Marcia

Private Sub CommandButton1_Click()

Dim wsht As Worksheet
Dim myshts As Range
Dim allowshts As Sheets
Dim mycel As Range
Set allowshts = Worksheets
Dim mytothours As Integer

For Each wsht In allowshts
Set myshts = wsht.Range("e21")
For Each mycel In myshts
If mycel.Value > 0 Then
mytothours = mytothours + mycel.Value
End If
Next mycel
Next wsht

End Sub
Click to expand...
Click to expand...
 
Top