add cell across all worksheets

[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

 

[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)

 

[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

 

[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.

 

[Marcia]

Thanks so much. That solved it.

Marcia

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

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

 

[Guest]

if you need to have the data copied to multiply sheets then you can use the
SUM function. eg =SUM('SheetName'!E21) But if all your going to do it
calculations with it, you can referance it from the main sheet without coping
it.