Help!!Need Macro which don't allow any value to disappear.

Dear All Experts ,

I Need your help to fix this real issue with my workbook. I am really worried how it can be fixed, I have a Workbook with 2 sheets named as "Data" and "Records" , sheet1 "Data" which has all the information and sheet 2 "Records" where formula's update the calculations from "Data" sheet when the status changes to Deposit ,Cash, Done, for particular month/ year , so far everything was working perfectly by using formula for calculations for making yearly reports.

but the things not permanent in "Record" sheet as soon as I make changes on my "data" sheet again ,things are also changing in "Records" sheet which I really don't want. it should only add up thing without subtracting anything.

Once Any values coming in "Records" sheet it should be permanent and should not go away even after making any changes like again status changed on "data" sheet till the time things not deleted manually in "Records" sheet

Need any macro which can make this thing possible ....

I have attached a sample workbook .....please Help..

Thank you..

Originally Posted by kvsrinivasamurthy

Sheet RECORDS Column K has formulas.

Code for macro

Thank you soo much kvsrinivasamurthy for you time and effort ,I really Appreciate your Help.

We are in right track so far things are working and recorded as I want the only missing thing are:

1) It should also allow addition over a recorded value over and over without reverting anything on status changed.

let suppose for Category Remote column = we have 5 amounts scheduled for the same month , once there status changes to "cash", "deposit", "done" one by one for same month then the recorded value should add up with a new amount over a old amount (Sum of old recorded amount + new amount) over a recorded value when its status changes.

2) Do I have to run macro from view code over and over, Can?? sub macro be called on workbook open module? it is not recording by itself when the status changes .

3) While running the macro for view code its like a refresh to recording sheet and placing new data over it ...I want those value to be added over old values.

Thank you soo much

Try with this

Please Login or Register  to view this content.

Kind regards
Leo

Originally Posted by LeoTaxi

Try with this

Thank you soo much LeoTaxi...The code given by you is working Perfect as I wanted Thank you soo much for your help..

one more thing in my original Worksheet I have other code too and a Userform to open the worksheet too.

Private Sub Worksheet_Change(ByVal Target As Range) =====I already have this name existed name in my worksheet with other code ...if I want to copy your code in my original worksheet ,then what will be the process to add Two
Private Sub Worksheet_Change(ByVal Target As Range)????

i can say it, after i have seen this code


Kind regards
Leo

In this a form appears when there is no date


Kind regards
Leo

Thank you Soo Much Leo , it work amazing ...