Cell Value in one sheet using SUM formula in another
 

Am using code below to loop through worksheets in workbook. Objective
is to place value in cell where that value is determined by the sum of
a range of cells on another sheet.

My current code is:

For Each sh In ThisWorkbook.Worksheets
If sh.Name "000" Then
Last = LastRow(DestSh)
DestSh.Cells(Last + 1, "A").Value = sh.Name
DestSh.Cells(Last + 1, "C").Value = _
sh.Range("E46").Value + sh.Range("F46").Value + sh.Range("G46").Value
DestSh.Cells(Last + 1, "D").Value = _
sh.Range("H46").Value + sh.Range("F46").Value
End If
Next

Instead of:
DestSh.Cells(Last + 1, "C").Value = sh.Range("E46").Value + _
sh.Range("F46").Value + sh.Range("G46").Value

I would like DestSh.Cells(Last + 1, "C").Value = SUM("E46:G46") on sh.

Thanks In Advance...

Mike

Cell Value in one sheet using SUM formula in another
 

DestSh.Cells(Last + 1, "C").Value = Application.SUM(sh.Range("E46:G46"))

--
Regards,
Tom Ogilvy


"Mike" wrote in message
ups.com...
Am using code below to loop through worksheets in workbook. Objective
is to place value in cell where that value is determined by the sum of
a range of cells on another sheet.

My current code is:

For Each sh In ThisWorkbook.Worksheets
If sh.Name "000" Then
Last = LastRow(DestSh)
DestSh.Cells(Last + 1, "A").Value = sh.Name
DestSh.Cells(Last + 1, "C").Value = _
sh.Range("E46").Value + sh.Range("F46").Value + sh.Range("G46").Value
DestSh.Cells(Last + 1, "D").Value = _
sh.Range("H46").Value + sh.Range("F46").Value
End If
Next

Instead of:
DestSh.Cells(Last + 1, "C").Value = sh.Range("E46").Value + _
sh.Range("F46").Value + sh.Range("G46").Value

I would like DestSh.Cells(Last + 1, "C").Value = SUM("E46:G46") on sh.

Thanks In Advance...

Mike

Cell Value in one sheet using SUM formula in another
 

Tom....many thanks. Wish I could meet you someday...kind regards