Adding cells from 2 sheets to a third cell without value in cell changing

[FDPetey]

Hello all, first time I've posted and asked for help. I'm trying to make a spreadsheet for end users where they can enter a value into a cell on sheet 1 and in sheet 2, the values are added together for review by admin. That part is simple and working however, the problem I'm looking to fix is that sheet 1 will have new values entered weekly and the value in sheet 2 will need to be added onto so a monthly and quarterly running total is produced where other formulas can be run against that number. I basically just can't figure out how to keep a running total on sheet 2 as weekly entries are made on sheet 1.

Hope I explained this well.

Thanks in advance for any help.

 

[FDPetey]

This is a better way to perform this task with only about 20 lines of code.
and will do as many rows as needed.
I have the script being able to start on row 3 till row 900
You can change this as needed. I figured the first three rows may be headers.

I want you to enter your value into column F and then when you enter you enter your value into column E the script will run.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 8/21/23 4:15 AM EDT
If Target.Column = 5 Then
Dim From As String
From = "Training Hour Entry Sheet"
Dim Too As String
Too = "Incentive Pay Calculation Sheet"
Dim i As Long
Dim ans As Long
Dim anss As Long

For i = 3 To 900
Select Case Target.Row
Case i
Sheets(Too).Cells(i, 6).Value = Sheets(From).Cells(i, 5).Value + Sheets(Too).Cells(i, 6).Value
Sheets(Too).Cells(i, 7).Value = Sheets(From).Cells(i, 6).Value + Sheets(Too).Cells(i, 7).Value
End Select
Next
End If
End Sub

Good deal. This mostly worked. Cell (i,7) wouldn't populate the way you wrote it so I had to do some figuring and through trial and error, I came up with the below that works great. Thank you

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 5 Then
Dim From As String
From = "Training Hour Entry Sheet"
Dim Too As String
Too = "Incentive Pay Calculation Sheet"
Dim i As Long
Dim ans As Long
Dim anss As Long

For i = 2 To 900
Select Case Target.Row
Case i
Sheets(Too).Cells(i, 6).Value = Sheets(From).Cells(i, 5).Value + Sheets(Too).Cells(i, 6).Value
End Select
Next
End If


If Target.Column = 6 Then
From = "Training Hour Entry Sheet"
Too = "Incentive Pay Calculation Sheet"


For i = 2 To 900
Select Case Target.Row
Case i
Sheets(Too).Cells(i, 7).Value = Sheets(From).Cells(i, 6).Value + Sheets(Too).Cells(i, 7).Value
End Select
Next
End If
End Sub

 

[My Aswer Is This]

Looks like you modified it a lot.
But if your happy that what counts.
I see you wanted the script to run when you change value in column 5 or 6 which I did not see the need for.
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.