Correct Macro for Totals
 

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

--
Message posted via http://www.officekb.com

Correct Macro for Totals
 

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

"franklin via OfficeKB.com" <u47278@uwe wrote in message
news:8dde310471a2b@uwe...
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

--
Message posted via http://www.officekb.com

Correct Macro for Totals
 

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



Bob Phillips wrote:

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

"franklin via OfficeKB.com" <u47278@uwe wrote in message
news:8dde310471a2b@uwe...
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

--
Message posted via http://www.officekb.com


--

Dave Peterson