Hi,

I use the following code to find the last cell in column B and then su
the total beneath the last row of data.

Dim firstcell%
Dim n%
Dim theOldTotalAddress%
Dim therow%

firstcell = 2

Sheets("sheet1").Select

Range("B" & firstcell).Select
Do Until IsEmpty(ActiveCell.Value)
n = n + 1
Range("B" & firstcell + n).Select
Loop


ActiveCell.FormulaR1C1 = "=SUM(R[-" & n & "]C:R[-1]C)"
therow = ActiveCell.Row

'Border
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone

'Total
Range("A" & therow).Select
ActiveCell.Formula = "Total"
End Sub

1st issue:
This works great when I sum just one sheet, however, when I try to su
multiple sheets (I use three sheets), the sum on Sheet 2 will b
correct, however the placement of the total line will be incorrect.
For example if Sheet 1 has 5 rows, the total will be on Row 6 (This i
correct) on Sheet 2 there are 10 rows, the total should be on row 11
however it is on row 16 with 5 blank rows(I assume it is counting th
Sheet 1 rows..but not adding the data because the total is correct)
Any suggestions?

2nd issue:
If there is no data on a particular sheet, the total is creating
circular reference is summing B2:B65525. Is there a way to put 0.00 i
Cell b2 if no data?

Thanks for any help

--
STEVE
-----------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...nfo&userid=187
View this thread: http://www.excelforum.com/showthread.php?threadid=48360