Macro to Copy and Paste Value based on cell changing value

I... i'm dealing with a problem here....
I have a workbook that has a formula in cell J20 of sheet "MONEY", that based on number inputs in other cells, makes J20 changes its value....
The thing is... over a session where i write down values in other cell, J20 starts variating his value but i want to get the maximun value it reaches. But if i use MAX formula on J20 it varies its max value because its not a range and only a single celll. So i though of buiding a macro where in the same sheet "MONEY" or active sheet everytime that J20 changes its value the macro copy J20 value to cell BK8, BK9, Bk10... so i can a range where i can use the formula MAX(BK8:B1000) for example and now i could obtain the maximun value that J20 showned in a session. Because when i stop wroting values i press a delete macro i built that erases all data and J20 gets to zero again. So the macro to get copy/paste values of J20 must know when i press the erase macro to start coping again the value to BK8, BK9, BK10.... and not to continue from the last one.....
I dont know if i made myself clear or if there is another method of doing this, because what i really need i to get the maximum value that J20 reaches when it starts from zero value.
Thank you.

@davesexcel

Hi. Thank you for your response. I added a sample workbook where i show an example of the cell who changes values (cell C11). Its value varies beetween the values i input in cell ranges B6:b10 and D6:D10. I want to get the max of C11 gets to cell C1. But i want this cell to show value 0 if i erase the data in range B6:B10. Please see the sample workbook.
Thanks

I can make this work, but for that i have to make a circular reference and i wanna try to avoid that...
So thats why i though if eveytime that that specific cell changes its value (in the sample workbook cell C11) a macro copies its value and past in a column like D1, than C11 changes again, the macro copy again and paste the value in D2, and so on... and like that in another cell i'll just put a formula to retrive the max value in range for example D1:D10. But i dont know how to do this... i'm already growing some white hair of thinking this. Yesterday i went to bed at 3 am just trying to solve an equatioon about the circular reference so i can avoid it and make a formula. Because circular reference its all about solving an equation. The thing is that this one is quite complicated and i cannot solve it. So thats why i though about a macro that detects ONLY changes in cell C11 and if so copy its value and paste on D1, then D2 then D3 and so on. This way i could get the maximum value that C11 reaches during inputs of numbers in the table.

Please someone help. I cant think of anything else.

I've been thinking of a macro to detect cell C11 changes and so copy then paste its value on Collumn I so i can get a range of C11 values and then get the maximum
So i built this macro but its not working.... can someone please help me?

Please Login or Register  to view this content.

@mrice

Once again you are great.... i solve it through another macro that keeps records in another sheet everytime i input a value that determine the value of the target cell. Then i get a list of all values that target cell received so i can use MAX formula next and it works... But your macro is all i wanted from the beginning.
Many many thanks
Really helpfull.

Regards and thank you once again. You're a PRO!