Sum on Multipel sheets

[STEVEB]

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!

[Rowan Drummond]

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!
>
>

[STEVEB]

Rowan,

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

[Rowan Drummond]

You're welcome.

STEVEB wrote:
> Rowan,
>
> Thanks you for your help! Everything worked great & I really
> appreciate it! You save me so much time!!
>
>