Keep total of cells in a cell after deleting source data

[CVinje]

I'm trying to create a summation sheet of the overtime hours worked by
employees, but don't want to track the actual hours / days they worked on
this sheet, just the total hours they've worked over the year. I'd like to be
able to have cells for each day of the week in the columns, and the person
name in rows. For each day of the week, you could then enter any overtime
worked. I've come across a macro that accomplishes what I need should I be
entering data for just a single cell (titled the OnEntry macro); however, I
need to create a sum for the week and have that total added to the previous
week.

Example problem:

John = 22 Hours of overtime in the year
Dave = 12 Hours of overtime in the year
Amy = 36 Hours of overtime in the year

This week John worked 12 hours of overtime on Monday, and 12 hours on
Friday, while Dave and Amy worked none.
I would like to be able to simply enter 12 in the cell corresponding
with John & Monday as well as John & Friday, get the total for those two
days, and add them to the 22 hours John has already worked in the year. Then,
have that number retained (now 46 hours instead of 22) while I clear out the
hours and move onto the next week.

Any help would be appreciated.

CVinje

 

[Don Guillett]

As ALWAYS, post YOUR code for comment and suggestions. Also, give layout and
before/after examples.

 

[CVinje]

There is no before and after example - I'm not sure what you're requesting by
that?

==================================================

Layout example:

Name Mon Tue Wed Thur Fri Sat Sun Week Total
Year Total
John 12 0 0 0 12 0 0
24 46
Dave 0 0 0 0 0 0 0
0 12
Amy 0 0 0 0 0 0 0
0 36

1)The year total column would automatically add the hours from the week.
(John had 22 hours before the input of the overtime)
2)The Year total retains it's value once the overtime for the week is removed.
3)The week total column would zero once the hours worked in the week are
removed.

===================================================

Code example for retaining cumulative value in a single cell (not what I
need):

Code from Microsoft.com: http://support.microsoft.com/default.aspx/kb/213445

' The Autpen name forces this macro to run every time
' the workbook containing this macro is opened.

Sub Autpen()
' Every time a cell's value is changed,
' the RunningTotal macro runs.
Application.OnEntry = "RunningTotal"
End Sub

'----------------------------------------------------------
' This macro runs each time the value of a cell changes.
' It adds the current value of the cell to the value of the
' cell comment. Then it stores the new total in the cell comment.
Sub RunningTotal()

On Error GoTo errorhandler ' Skip cells that have no comment.

With Application.Caller

' Checks to see if the cell is a running total by
' checking to see if the first 4 characters of the cell
' comment are "RT= ". NOTE: there is a space after the equal
' sign.
If Left(.Comment.Text, 4) = "RT= " Then

' Change the cell's value to the new value in the cell
' plus the old total stored in the cell comment.
RT = .Value + Right(.Comment.Text, Len(.Comment.Text) - 4)
.Value = RT

' Store the new total in the cell note.
.Comment.Text Text:="RT= " & RT
End If
End With

Exit Sub ' Skip over the errorhandler routine.

errorhandler: ' End the procedure if no comment in the cell.
Exit Sub

End Sub

'--------------------------------------------------------------
' This macro sets up a cell to be a running total cell.
Sub SetComment()
With ActiveCell
' Set comment to indicate that a running total is present.
' If the ActiveCell is empty, multiplying by 1 will
' return a 0.
.AddComment
.Comment.Text Text:="RT= " & (ActiveCell * 1)
End With
End Sub

 

[CVinje]

<<CORRECTED FORMATTING ATTEMPT>>

Layout example:

Name Mon Tue Wed Thur Fri Sat Sun Week Total Year Total
John 12 0 0 0 12 0 0 24
46
Dave 0 0 0 0 0 0 0 0
12
Amy 0 0 0 0 0 0 0 0
36

1)The year total column would automatically add the hours from the week.
2)The Year total retains it's value once the overtime for the week is removed.
3)The week total column would zero once the hours worked in the week are
removed.

 

[Don Guillett]

Sub keepyrtotal()
For i = 2 To Cells(Rows.Count, "j").End(xlUp).Row
Cells(i, "j") = Cells(i, "J") + Cells(i, "I")
'to clear mon:Sun
Cells(i, "b").Resize(, 7).ClearContents
Next i
End Sub

 

[CVinje]

Thank you sir for the help - with some modification, this works perfectly!!

 

[Don Guillett]

Worked properly, as tested. Post your modifications.

 

[CVinje]

You misunderstand sir; the modifications were required because I provided a
simplified example of the spreadsheet. My actual sheet contains much more
information; and the cells the provided macro references do not correspond.
The modifications I was referring to were to accommodate this delta.

Thank you for your time and the offer for more assistance should I had
needed it. It is truly appreciated and I apologize for improperly phrasing my
reply and not making it more clear that your provided solution did indeed
work perfectly for my needs.


CVinje

 

[Don Guillett]

Glad to help