Sum on Multipel sheets
 

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

Sum on Multipel sheets
 

I have revised your code a bit. My version determines the last used row
in column B by imitating the cell that would be selected if you clicked
in Cell B65536 and then hit Ctrl+UpArrow.

Sub Totals()
Dim eRow As Long
Dim i As Integer
For i = 1 To 3 'adjust as required. Currently first 3 sheets
With Sheets(i)
eRow = .Cells(Rows.Count, 2).End(xlUp).Row + 1
If eRow 2 Then
With .Cells(eRow, 2)
.FormulaR1C1 = "=Sum(R[-" & eRow - 2 & "]C:R[-1]C)"
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
End With
End With
.Cells(eRow, 1).Value = "Total"
Else
With .Range("B2")
.Value = 0
.NumberFormat = "0.00"
End With
End If
End With
Next i
End Sub

Hope this helps
Rowan

STEVEB wrote:
Hi,

I use the following code to find the last cell in column B and then sum
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 sum
multiple sheets (I use three sheets), the sum on Sheet 2 will be
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 is
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 the
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 a
circular reference is summing B2:B65525. Is there a way to put 0.00 in
Cell b2 if no data?

Thanks for any help!

Sum on Multipel sheets
 

Rowan,

Thanks you for your help! Everything worked great & I reall
appreciate it! You save me so much time!

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

Sum on Multipel sheets
 

You're welcome.

STEVEB wrote:
Rowan,

Thanks you for your help! Everything worked great & I really
appreciate it! You save me so much time!!