VBA SUM multiple cells across worksheets

[BBCTodd]

Greetings all,

I don't know why, but my brain isn't working on this one, and I've been stuck for a few days.

I'm trying to SUM two cells from two worksheets in the same workbook and replace one of the cells with the result. (I.e. to form a running total)

Worksheet1 is an "input form" where the operators (they have absolutely zero knowledge of computers) will input their daily data of "jobs"

I.e.
Worksheet1 - Cell C1 - Lines Running: 5
Worksheet1 - Cell C2 - Fans Used: 50
Worksheet1 - Cell C3 - Boxes Opened: 10
etc.

They then hit a "Input and Save Data" button to automatically enter the data.

Worksheet2 is a "summary form" where it will keep a running total for a month of the number of times each "jobs" is done.

I.e.
Worksheet2 - Cell C1: Lines Running = SUM(Worksheet1!C1,Worksheet2!C1)
Worksheet2 - Cell C2: Fans Used = SUM(Worksheet1!C2,Worksheet2!C2)
Worksheet2 - Cell C3: Boxes opened = SUM(Worksheet1!C3,Worksheet2!C3)

this vba code would calculate the total and put the resulting number in Worksheet2!C3 whenever they use the button I put on the page to "Enter Data".


Thanks everyone for the help!

 

[Andrew Poulsom]

Welcome to MrExcel.

Not an answer to your question, but I would avoid that approach, as it provides no audit trail. What if the user clicks the button more than once? You should have a list of all transactions with a date or reference number.

 

[skorpionkz]

why you using SUM? just add values

sheets("Worksheet1").range("C1")+sheets("Worksheet2").range("C2")

 

[skorpionkz]

Welcome to MrExcel.

Not an answer to your question, but I would avoid that approach, as it provides no audit trail. What if the user clicks the button more than once? You should have a list of all transactions with a date or reference number.

Agree with that.
@BBCTodd you can add the "now()" value to the next column. this will help you to check totals for the month if will be needed

 

[BBCTodd]

I have it set in the macro that when they click the input button, it goes back and clears all cells on the input form. I have to make this as "idiot proof" as possible. So all the techs have to do is input their numbers, hit the button, and it'll be ready for the next shift.

Unfortunately the techs have a tendency to cut&paste, and alter the worksheets other ways. I can't lock the formulas and everything since it's for a company and the techs have access to everything. However, they are fairly computer illiterate, so to prevent tampering I have to hide most all formulas within VBA code. (Plus save a backup copy for when they mess it up beyond repair)

As for the "now()" command, the form will be summarized and cleared monthly, so they don't need any reference.

 

[Andrew Poulsom]

The you can use code like in Post #3.

 

[BBCTodd]

And awesome Andrzej (Andrew) Bejmart, adding the values works perfectly. I totally got fixated on SUM and forgot about the values. Thank you so much.

Thank you Andrzej (Andrew) Bejmart and Andrew Poulsom, for your quick and excellent responses.