ExcelBanter - Sum Column

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Sum Column (https://www.excelbanter.com/excel-programming/340725-sum-column.html)

Does anyone have any suggestions for:

1) Sum all cells in column B and put the total after the last cell.
The range changes daily so the last row # is unknown.

2) Put the text "Total" in column A next to the sum of column B.

3) Format the last cell (The sum of column B) with a double line at th
bottom.

4) Copy the sum of column B from sheet 1 to Sheet 2 Cell A2.

Any help would be greatly appreciated

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

Sub MakeTotal()
Dim rng As Range
With Worksheets("Sheet1")
Set rng = .Cells(Rows.Count, 2).End(xlUp)
End With
With rng.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
rng.Offset(1, 0).FormulaR1C1 = "=Sum(R1C1:R[-1]C)"
' Optional - replace formula with value
'rng.Offset(1,0).Formula = rng.Offset(1,0).Value
rng.Offset(1, -1).Value = "Total:"
Worksheets("Sheet2").Range("A2").Value = _
rng.Offset(1, 0).Value

End Sub

--
Regards,
Tom Ogilvy

"STEVEB" wrote in
message ...

Does anyone have any suggestions for:

1) Sum all cells in column B and put the total after the last cell.
The range changes daily so the last row # is unknown.

2) Put the text "Total" in column A next to the sum of column B.

3) Format the last cell (The sum of column B) with a double line at the
bottom.

4) Copy the sum of column B from sheet 1 to Sheet 2 Cell A2.

Any help would be greatly appreciated.

--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=469558

The following might work for you.

It consists of three macros. The first totals the columns and places the
correct value in the cell. The other two are to do with adding and removing
the formatting.

The one part it does not allow for is wiping out previous values which
exceed the length of the current days values but that should be easy enough
to rectify.

Hope this helps

June

Sub TotalTheCells()
'This macro will find the first cell in column B which is empty and place
the total value there
'

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

'You should set the value of firstcell to the row number
'in column B where you always place your first line of data

firstcell = 3

'I have assumed that the sheet where you put your data is
'named sheet1. If not then adjust the macro.

Sheets("Sheet1").Select

Range("B" & firstcell).Select
Do Until IsEmpty(ActiveCell.Value) 'loops through each row in turn until
it comes across an empty cell
If Selection.Borders(xlEdgeBottom).LineStyle = xlDouble Then
'this next part removes any old borders from previous runs
RemoveBorder
theOldTotalAddress = ActiveCell.Row
End If
n = n + 1
Range("B" & firstcell + n).Select 'adds one to the row count and starts
the loop again
Loop

'it has now come to an empty cell and will
'place the formula for the sum in it

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

'the next part sets the border on the active cell
SetBorder

'It now selects the appropriate row in column A and places "Total" in it
Range("A" & therow).Select
ActiveCell.Formula = "Total"

'If a previous run has left "Total" in another cell it will
'blank it out - provided it has occurred before the new total row
If Not IsEmpty(theOldTotalAddress) Then
Range("A" & theOldTotalAddress).Formula = ""
End If

'Selects the second sheet and puts a reference to the total into it.
Sheets("Sheet2").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[" & therow - 2 & "]C[1]"

End Sub

Sub SetBorder()
'
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

End Sub
Sub RemoveBorder()
'
'
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub

"STEVEB" wrote in
message ...

Does anyone have any suggestions for:

1) Sum all cells in column B and put the total after the last cell.
The range changes daily so the last row # is unknown.

2) Put the text "Total" in column A next to the sum of column B.

3) Format the last cell (The sum of column B) with a double line at the
bottom.

4) Copy the sum of column B from sheet 1 to Sheet 2 Cell A2.

Any help would be greatly appreciated.

--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=469558

Thanks Tom,

Everything worked great, I really appreciate your help. Is there a wa
to have the text "Total" in the column A in the cell next to the sum o
column B?

Thank

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

Thanks June,

I appreciate your help! Your example worked great! Is there a way t
have the code paste a value in sheet two rather than reference th
cell?

Thank

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

this line in his code does that
rng.Offset(1, -1).Value = "Total:"

--

Gary

"STEVEB" wrote in
message ...

Thanks Tom,

Everything worked great, I really appreciate your help. Is there a way
to have the text "Total" in the column A in the cell next to the sum of
column B?

Thanks

--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=469558

Replace the following lines:

Sheets("Sheet2").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[" & therow - 2 & "]C[1]"

with

Range("B" & therow).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Application.CutCopyMode = False

"STEVEB" wrote in
message ...

Thanks June,

I appreciate your help! Your example worked great! Is there a way to
have the code paste a value in sheet two rather than reference the
cell?

Thanks

--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=469558

Hi June,

Replace the following lines:

Sheets("Sheet2").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[" & therow - 2 & "]C[1]"

with

Range("B" & therow).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Application.CutCopyMode = False

Or, perhaps:

Sheets("Sheet2").Range("A2").Value = _
Sheets("Sheet1").Range("B" & therow)

---
Regards,
Norman

"June Macleod" wrote in message
...
Replace the following lines:

Sheets("Sheet2").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[" & therow - 2 & "]C[1]"

with

Range("B" & therow).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Application.CutCopyMode = False

"STEVEB" wrote in
message ...

Thanks June,

I appreciate your help! Your example worked great! Is there a way to
have the code paste a value in sheet two rather than reference the
cell?

Thanks

--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
View this thread:
http://www.excelforum.com/showthread...hreadid=469558

THANK YOU TO EVERYONE! The code worked great, I appreciate all you
help!

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