Excel 2010 - VBA - calculate and Copy paste value upon data entry in another cell

[lantuan]

Hello all,

I am seeking support for VBA, I am trying to create VBA code that copies and pastes data value, after data entry in another cell from the same row, I do not have great experience with VBA and so I am struggling to create such a code.

please have a look to the attached file and picture.

When entering a mouvement "in" colum C, or mouvement "out" in column D,
I have a vlookup to a pivot table (with automatic data refresh after each data entry) in separate sheet that gave me the current stock (column E ), this only give me the sum of stock entry minus sum of stock release from the pivot table. .


I would like to have the stock before and after when adding a quantity in or out (Displayed in Red in table below) Something like:
Once I enter data in col D or C
for the same row
Copy-paste value of stock current (after data entry), in cell stock after.
Calculate and copy paste value of : stock after+OUT-IN, in cell stock before.


The vloookup on pivot table will continue to give me the current stock, and the column stock before and stock after, will gave me the stock situation at the moment of the movement (so they should have value only, no formulas)


I do not want to ask too much, but as i am very limited , can you also kindly write also the "Dim." needed if any,
My Workbook name is : stock antoine.xlsm
My Worksheet name is : Stock - Mouvements


So far the only Macro I have is to update after udpate the pivot table with the following code :

Private Sub Worksheet_Change(ByVal Target As Range)
'refresh pivot after any changes in sheet Stock balance
ThisWorkbook.RefreshAll
End Sub

On top of that i would like to have a sometime that prevent me from entering data in the columns C and D, it should be either a "Quantity In" or "Quantity out", something like: If the other cell is not empty then forbid data entry .

Thank you very much in advance for your help


Antoine
Humanitarian worker

table.png