Correct Macro for Totals

[franklin via OfficeKB.com]

Hi All

I am trying to use this macro shown below to produce a total line at the
bottom of each sheet I am using.

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet1").Activate
ActiveCell.SpecialCells(xlLastCell).Select
Range("E10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)"
Range("E10").Select
Selection.AutoFill Destination:=Range("A10:E10"), Type:=xlFillDefault
Range("A10:E10").Select
End Sub

The problem is the the number of rows varies from each sheet, so the totals
will be in the wrong place for most of the sheets.
Is there any way that the total per column can be offset so they are say 2
rows below the bottom number on each sheet?

Many thanks

franklin

 

[Bob Phillips]

For Each sh In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))

For i = 1 To 5

nRow = sh.Cells(sh.Rows.Count, i).Row
sh.Cells(nRow + 2, i).FormulaR1C1 = "=SUM(R1C:R[-1]C)"
Next i
Next sh

 

[Dave Peterson]

I think Bob left off an ".end(xlup)" in his code.

This version uses the data in column E to determine the last row to add and it
doesn't leave an empty row between the last detail row and the total row:

Option Explicit
Sub testme()

Dim sh As Object
Dim i As Long
Dim nRow As Long

For Each sh In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
nRow = sh.Cells(sh.Rows.Count, "E").End(xlUp).Row
For i = 1 To 5
sh.Cells(nRow + 1, i).FormulaR1C1 = "=SUM(R1C:R[-1]C)"
Next i
Next sh

End Sub

For Each sh In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))

For i = 1 To 5

nRow = sh.Cells(sh.Rows.Count, i).Row
sh.Cells(nRow + 2, i).FormulaR1C1 = "=SUM(R1C:R[-1]C)"
Next i
Next sh

--
__________________________________
HTH

Bob

Hi All

I am trying to use this macro shown below to produce a total line at the
bottom of each sheet I am using.

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet1").Activate
ActiveCell.SpecialCells(xlLastCell).Select
Range("E10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)"
Range("E10").Select
Selection.AutoFill Destination:=Range("A10:E10"), Type:=xlFillDefault
Range("A10:E10").Select
End Sub

The problem is the the number of rows varies from each sheet, so the
totals
will be in the wrong place for most of the sheets.
Is there any way that the total per column can be offset so they are say 2
rows below the bottom number on each sheet?

Many thanks

franklin