Need help copying and pasting cell values on cell change

[ghaulk]

Hello all,

I need help in copying cell values when a cell value changes. Background: I have developed a workbook that is linked to several different servers. Each sheet in the worbook is linked to a specific server. The servers are basically PLC's in a plant. I have running totals that are being monitored. Once the job is finished, the operator on the line presses a button and this captures a time stamp sent from the PLC. When this time (value) changes, I want to copy all of the data in a group of cells and paste them in cells below the "Running Totals". I want to do this 4 different times. In other words, I want to be able to capture the totals for the last 4 jobs that were run. I am somewhat familiar with VB so if there is a macro out there that will work, I should be able to modify it. I am using Excel 2010.

Thanks

[Tsjallie]

In the code module of the worksheet containing the data enter the following code:

Please Login or Register  to view this content.

This event will be triggered each time the content of a cell changes.
The intersect construct is meant to avoid handling changes which are not relevant to you.
If there are more ranges of which you want to capture the changes you can also use a select case iso if then.
Do consider that a recalculation of a formula does not change the content of a cell (which is the formula itself) and thus will not trigger the event.
Would this get you started?

[ghaulk]

Yes, most certainly! I may still need some help with the Case Statement, but I will give this a shot. Thanks for the quick response.

[ghaulk]

OK. I got the sheet working like I want. Well, almost. I can change the value of the target cell and it copies and pastes ecah time. I have it where it will index each time it copies. What I need now is for the code to recognize the cell change when it is automatically updated. (via the server) I did a test and put a formula in the target cell to just add the value of two other cells and it did not trigger the copy/paste code. Any suggestions would be appreciated.

[Tsjallie]

When a formula is recalculated the cell may show a new value, but the actual content of the cell - which is the formula itself - isn't changed. And thus the worksheet_change event is not triggered.
To intercept a calculation you need the worksheet_calculate event, but that won't give you a clue about which cell has been recalculated. Excel is not very helpfull on that, so that takes some vba-code.
I'll come back on that tomorrow as for now I'm suffering a terrible cold and only want to go the bed with stiff drink

[Tsjallie]

Here's a sample with a possible solution to intercept calculated cells.
This solution depends on some global variables. Remember that variables use memory. If there are a lot of cells with formulas to monitor then you may consider to use a sheet to hold the values instead of global variables. Also remember that when opening the workbook these variables are empty and need to be initialized (preferably) in the ThisWorkbook_Open event.

[ghaulk]

Thank you for responding while you are sick. I hope you are feeling better. I cannot open the document. Excel says the file is corrupted.

[Tsjallie]

Hmm, cannot reproduce the error you encountered.
But no problem, you can easily create the demo yourself with the following instructions.

1. Create a new workbook or open the workbook you're already playing with (if any).
2. Enter the following code in the Thisworkbook code module
   

   Please Login or Register  to view this content.

3. Create a module and enter the following line in it:
   

   Please Login or Register  to view this content.

4. Enter 2 formulas (of your choice) and name the cells containing them "Formula1" and "Formula2" respectively.
5. Enter the following code into the code module of the worksheet containing the formulas
   

   Please Login or Register  to view this content.

Together with the Worksheet_change event you can now control any change in your worksheet whether it is triggered by a change or by a recalculation.

[ghaulk]

Tsjallie,

Thank you for the code snippets. I will do some experimenting with them. Funny enough tuough, I decided to go ahead and implement my code using some cells that are linked to the server I am communicating with and it works perfectly when the server send up a new date to be placed in the cell I am using to initiate the copy/paste function. Unless something unexpected happens, I am all set. Thank you very much for your help and I hope you are over your cold. (or at least found that stiff drink) I would be happy to upload the code I am using if you would like. Just let me know.

Thanks again

[Tsjallie]

Glad to hear that your on track. And yes I do want to take a look at your workbook.
If you have more questions, do post 'm.