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]

So, the value in sheet Summary range F2 and G2 would always need to added to correct?
So in your example if shows 100 so next time you run script that value would increase in value from 100 to whatever it may be in the other sheet next time it was run. That other sheet name is way too long for me to remember without looking back. and I assumed the value in column H gets it's results from a formula in that cell.

Okay, sorry but I'm a little confused on what your asking exactly. Let's start by simplifying the long sheet name by just calling it Sheet("Training"). I can change the name of the sheet in the script later.

The now named Sheet("Training") is where the hours are entered for each employee. Correct me if I'm wrong but the way I understand the script will work is after the user enters hours into any of the E or F column cells, the user would then hit a "submit" button (or whatever the button is called) and those hours will add to the total in it's corresponding H column cell in Sheet("Summary").

Ex: If the user enters values of 100 in E2 and 100 in F2 for employee 1 and then enters 50 in E3 and 100 in E4 on Sheet("Training"), then clicks a submit button, H2 and H3 on Sheet("Summary") will update. 200 will be added to H2 and 150 will be added to H3. The total hours in the H column will continue to add without resetting to the starting amount shown in the E column on Sheet("Summary"). Same as we did before only with multiple employees on the same sheet instead of each employee having their own sheet.

The F and G column cells in Sheet("Summary") are just mirrored cells from the E and F column cells of Sheet("Training") so that the auditor can see what was entered last. Realistically those F and G cells don't need to be there. I only have them in there in case a mistake in hours is made and submitted, the auditor will know how many hours were entered last so he will have a value of the last entry to base his adjustments.

 

[My Aswer Is This]

No, I thought you said it would be OK to run a script that would do all 800 of them when you click on a button that has the script I provide.
You're now saying you may have as many as 800.
we cannot realistically have 800 cell change event scripts.
But we could have one script that runs through all 800 cells at the same time.

 

[My Aswer Is This]

If you want the script to run when someone enters a value into a cell.
Then we need to have the same row on the Master sheet also be the same row on the summary sheet.
That would be somewhat easy.
And actually, the safe way would be for us to use a double click event script.
You double click cell A5, and the script sends the data on row 5 to the same row on the Summary sheet. Double clicking is safer because a user would normally not accidently double click on a cell.

 

[FDPetey]

No, I thought you said it would be OK to run a script that would do all 800 of them when you click on a button that has the script I provide.
You're now saying you may have as many as 800.
we cannot realistically have 800 cell change event scripts.
But we could have one script that runs through all 800 cells at the same time.

Yes, I believe the latter is what I'm thinking you were talking about originally. One script that runs through and updates all of the "H" column cells on Sheet("Summary") with the values entered on Sheet("Training"). That way it doesn't matter whether what value was entered in a "E" or "F" on Sheet("Training"). If it's 100, the corresponding "H" cell in Sheet("Summary") will increase by 100. If nothing is entered in "E" or "F", the value is read as a zero and the "H" cell value will remain the same.

 

[FDPetey]

If you want the script to run when someone enters a value into a cell.
Then we need to have the same row on the Master sheet also be the same row on the summary sheet.
That would be somewhat easy.
And actually, the safe way would be for us to use a double click event script.
You double click cell A5, and the script sends the data on row 5 to the same row on the Summary sheet. Double clicking is safer because a user would normally not accidently double click on a cell.

This could work so long as I can make the user aware of the double click. The only fear I have would be if they made the entries but forgot to double click some of the cells, the values wouldn't be carried over. I get that it's a good way for a user to check they're entry before committing it but the fear I have is if the user make multiple cell entries but forgets to double click some of the cells, the values won't commit and be carried over. I think the button clicking for submission works better for a user. People are used to seeing that type of action.

 

[My Aswer Is This]

You need to tell me how you want this to work.
We have talked about a sheet cell change event then a double click event and a script which will do all 800 of them at one time.
The script runs down column A for example and copies this rows data the sheet named "Summary".
But each way may cause you problems.
The sheet change event could cause a problem if a user enters a value like Alpha instead of a number like 56

This new feature on this forum where it does spell check and then tries to anticipate what you want to write drives me crazy.

 

[FDPetey]

You need to tell me how you want this to work.
We have talked about a sheet cell change event then a double click event and a script which will do all 800 of them at one time.
The script runs down column A for example and copies this rows data the sheet named "Summary".
But each way may cause you problems.
The sheet change event could cause a problem if a user enters a value like Alpha instead of a number like 56

This new feature on this forum where it does spell check and then tries to anticipate what you want to write drives me crazy.

I think we're getting thoughts crossed here with the presented ideas. I'll just keep this simple with the original idea that worked by creating 200 individual sheets, 1 for each employee. I'll then add the code you sent originally 200 times changing the name of the source for each sheet. It's quite laborious but we know it'll work.

Even though I appreciate all I am learning thank you very much for all you've done, it seems as though you may be getting frustrated so I'm thinking that me doing the massive amount of code is probably the best way so you don't have to waste any more time on me.

 

[FDPetey]

You need to tell me how you want this to work.
We have talked about a sheet cell change event then a double click event and a script which will do all 800 of them at one time.
The script runs down column A for example and copies this rows data the sheet named "Summary".
But each way may cause you problems.
The sheet change event could cause a problem if a user enters a value like Alpha instead of a number like 56

This new feature on this forum where it does spell check and then tries to anticipate what you want to write drives me crazy.

Got an easy question for you. I got all of the code into the file but it's not working telling me the procedure is too long. Is there some type of limit on how many can be done? If so, is there any fix for that like a separator or something? I checked the code on an earlier version and with only 5 entries it worked fine so I know I didn't type it incorrectly.

 

[My Aswer Is This]

Got an easy question for you. I got all of the code into the file but it's not working telling me the procedure is too long. Is there some type of limit on how many can be done? If so, is there any fix for that like a separator or something? I checked the code on an earlier version and with only 5 entries it worked fine so I know I didn't type it incorrectly.

If you have problems with code you need to post the code here so I can look at it.
If you look you will see an icon which Says VBA in this forum
You will also see B I and on and on Click on Vba and then post your code there.

 

[FDPetey]

You need to tell me how you want this to work.
We have talked about a sheet cell change event then a double click event and a script which will do all 800 of them at one time.
The script runs down column A for example and copies this rows data the sheet named "Summary".
But each way may cause you problems.
The sheet change event could cause a problem if a user enters a value like Alpha instead of a number like 56

This new feature on this forum where it does spell check and then tries to anticipate what you want to write drives me crazy.

Sorry, thought I should state the exact error - Compile error: Procedure too large. Also, when the error occurs, the first line - Private Sub Worksheet_Change (ByVal Target As Range) - is highlighted in yellow and - End Sub - is highlighted blue until you click of to the side.