SUM total into separate worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Martin - 20 Mar 2006 17:56 GMT

I would like to use this code to SUM the total of column G into a different
worksheet. How do I do this?

Thanks.

Set sh = Workbooks.Application.ActiveSheet
Set r = Range("G" & Rows.Count).End(xlUp).Offset(2, 0)
r.FormulaR1C1 = "=SUM(R2C:OFFSET(RC,-2,0))"

Reply to this Message

Tom Ogilvy - 20 Mar 2006 18:37 GMT

assume you mean column G in sheet1
Dim rng as Range, cell as Range
With worksheets("sheet1")
set rng =.range("G2",.cells(rows.count,"G").End(xlup))
end with
With worksheets("Sheet2")
set cell = cells(rows.count,1).End(xlup)(2)
cell.formula = "=sum(" & rng.Address(1,1,xlA1,True) & _
")"
End With

Signature

Regards,
Tom Ogilvy

> I would like to use this code to SUM the total of column G into a different
> worksheet. How do I do this?
[quoted text clipped - 4 lines]
> Set r = Range("G" & Rows.Count).End(xlUp).Offset(2, 0)
> r.FormulaR1C1 = "=SUM(R2C:OFFSET(RC,-2,0))"

Reply to this Message

Martin - 20 Mar 2006 21:05 GMT

Tom I'm getting the value on Sheet1.

assume you mean column G in sheet1
Dim rng as Range, cell as Range
With worksheets("sheet1")
set rng =.range("G2",.cells(rows.count,"G").End(xlup))
end with
With worksheets("Sheet2")
set cell = cells(rows.count,1).End(xlup)(2)
cell.formula = "=sum(" & rng.Address(1,1,xlA1,True) & _
")"
End With

Signature

Regards,
Tom Ogilvy

"Martin" wrote:

Reply to this Message

Tom Ogilvy - 20 Mar 2006 22:27 GMT

I left off a period. See revised:

Dim rng as Range, cell as Range
With worksheets("sheet1")
set rng =.range("G2",.cells(rows.count,"G").End(xlup))
end with
With worksheets("Sheet2")
set cell = .cells(rows.count,1).End(xlup)(2)
cell.formula = "=sum(" & rng.Address(1,1,xlA1,True) & _
")"
End With

Signature

Regards,
Tom Ogilvy

> Tom I'm getting the value on Sheet1.
>
[quoted text clipped - 18 lines]
> > Set r = Range("G" & Rows.Count).End(xlUp).Offset(2, 0)
> > r.FormulaR1C1 = "=SUM(R2C:OFFSET(RC,-2,0))"

Reply to this Message