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

[Pepe Le Mokko]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

As you are new here I will do it for you this time

[Pepe Le Mokko]

Hi and welcome
although we value privacy as much as anyone else, it could be important that members have a rough idea of your location. You might in the future post questions which are bound to your regional settings.
So, please update your profile to something more precise then "Groland" ( country will suffice, no need to be more precise).
Thank you for helping us to help you

[lantuan]

Thanks Pepe for the welcome, and for adjusting the Tag.
I have updated my Location.
Thank you for helping me.