Reference another sheet in code/creating command button to manually store old cell value

Disclaimer: I am not a programmer, I just find code and attempt to adapt it to my needs.

I found the following code that works for my purpose of saving updating cell values each week, but have run into two problems making it work for my spreadsheet:

1. I need to reference a second sheet for "A4" as A4 is not on the same worksheet as A1, but the code doesn't like something like "Calc"!A4 to point to the other sheet named Calc.
2. I need to create a button to do this "save" manually, but have never used ActiveX command button's before. Not sure where to start there. If its not manual, then it will log a saved entry into my A column on Calc sheet each time I enter a new number on other sheets as A1 is calculated from other cells.
Any help would be greatly appreciated!

Code I found that works partially: "Say we start with A1 and A4 both empty. The following Event macro will allow you to enter a value in A1 and have it appear in A4. Each time the value in A1 is changed, then new value will be copied into the first empty cell below A4. Enter the following event macro in the worksheet code area:"

Please Login or Register  to view this content.

Just based on the information provied

Please Login or Register  to view this content.

Should have said in my last post. But to have VBA look at a worksheet use

Please Login or Register  to view this content.

Similar to workbooks

Please Login or Register  to view this content.

Originally Posted by aworfolk

Just based on the information provided

Thanks for answering and attempting to solve my problem #1! But that didn't seem to work. I did change the cells to fit my spreadsheet. Did I change something incorrectly? The cells used are D11 on the sheet where I'm putting the code, and U3 on Calc sheet.

Please Login or Register  to view this content.

I found an error when testing a bit more

Please Login or Register  to view this content.

Paste the following in WBR (West)

Please Login or Register  to view this content.

Originally Posted by aworfolk

Paste the following in WBR (West)

Please Login or Register  to view this content.

Thanks but that didn't work either. I made sure to remove all code from the other worksheets as well. I appreciate your help and trying! Did it work for you on my spreadsheet?

Sorry I got busy with work. I have uploaded the file.

What I have done to the code is made it so when you up date the information on the west page
it will run the code since that seems to be the source of the information.

Originally Posted by aworfolk

Sorry I got busy with work. I have uploaded the file.

What I have done to the code is made it so when you up date the information on the west page
it will run the code since that seems to be the source of the information.

Sorry for the delay. Thank you very much for your work on this. The code you used definitely records the current variance number, however it doesn't record future variance numbers down the U column when we add weekly numbers to the next column ("WBR West" column ID) or the next one, and so on, on the WBR. Which is why I was wanting to tie it to the D11 number on the Stats sheet, because that number will always reflect the updated variance calculation. Does that make sense? This also will be done for the East variance ("Stats" I11).