how to capture changes in Cell

[vani2004]

I need help with capturing cell changes. I have two cells both has INDEX/Ranom formula in it. My questions is how do i capture those changes automatically into another sheet.

here is the formula that i have in cell and the formula is link with another sheet.

Cell D5 in Sheet1: INDEX('sheet2'!$B5:$B11,RANDBETWEEN(1,7))
Cell D6 in sheet1: INDEX('sheet3'!$A7:$A33,RANDBETWEEN(1,COUNTA('sheet3'!$A7:$A33)),1)

the above cells contain 2 formulas in one cell.
Its basically Cell D5 picking randon person names from another worksheet. In cell D6: Is Picking Job order that need to be perform by person in cell d5.

Is there a way to capture changes automatically everytime those two cell changes into another worksheet so once a week i can go over all changes. I know how to track changes a cell that doesn't have formula in it. but i guess because this cell has random formula track changes is not working. I'M LOST......can anyone help. Thanks

 

[Aaron Blood]

In this case, you want to code to the Calculate event rather than the Change event. Put this code in the code module for the sheet you want to track changes on and set "Sheet2" tracking sheet reference to something appropriate.

Option Explicit

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
        Dim NextCell As Range
        Set NextCell = Sheet2.Range("A65536").End(xlUp).Offset(1, 0)
        NextCell.Offset(0, 0).Value = Me.Range("D5").Value
        NextCell.Offset(0, 1).Value = Me.Range("D6").Value
    Application.EnableEvents = True
End Sub

 

[vani2004]

this is not working Aaron. I am doing something wrong here...i am not good at VBA at all. I cut and past this code into Module and main sheet. Also, how about sheet3?. can i email you file if you don't mind? Thanks

 

[Aaron Blood]

You can't just paste it in a standard code module, it's a worksheet event procedure.

In the VBE, in the project explorer section, you have to double-click the worksheet object in the Excel Objects folder to pull up the codepane for the worksheet module... that's where it goes. Delete it from any other places you might've copied it.

If all else fails, I could PM you an email address to send me the file.

 

[vani2004]

Aaron, Thank you very much...it is working however, its not working the way i want. Its making changes on the sheet that has already all the information and changes are writing over it.
1) I have sheet1 which is my Master sheet thats were all the changes are happening. (Master sheet contains Line A machine, Line B machine, Line C machine and information on this each 3 line changes 3 to 5 times a days )

2)there are other two sheets where master sheet is fulling information

3)Then there are 3 other sheets(lineA, Lineb, Line C) thats where i need to capture all the changes. for example, On master sheet if anything changes on lineA, changes need to go automatically on lineA sheet.

if you can pm me your email i will send you file so you get better idea...thanks for all the help.

 

[drmidnight]

i have a slightly different cell capture problem. I would be grateful if someone could help me out.

My cell values are changing dynamically in column A, a1 to a20 . I want to capture them 1 minute say after I've opened the worksheet and dump them to column c, so that i can process them further as i wish.
Then say 30s after the first capture, i want to capture column a again and dump these new values to column c , replacing the old column c values.
Then repeat the process every 30s until i type stop cell B1.