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.
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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 way to have the text "Total" in the column A in the cell next to the sum of column B?
Thanks
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
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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
> message news:[email protected]...
>>
>> 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
>>
>
>
this line in his code does that
rng.Offset(1, -1).Value = "Total:"
--
Gary
"STEVEB" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks