Add and subtract from another sheet

[Jasmin74]

Hello,
I have a workbook with two sheets
Sheet 1- Oldstock 2021-2022 : It contains warehouse information

Sheet 2- Transaction
we are selling & returning items stated
Question 1:
About the information we got from the first article
We have two processes: the sale (Sale) and the return (retrieval) and the quantity sold or returned
After putting the quantity sold, I want to make a confirmation in order to transfer NEWSTOCK to Quantity in stock on the first sheet.


Question 2:
2- How to add the current date automatically each time I add a new line
I put it now () but how is it written automatically

The file is attached.

test-0-fr-m.xlsm

drive.google.com

Thank you.

 

[Jasmin74]

It's not copying the NEWSTOCK value from TRANSACTION sheet when updating it into the Quantity stock in the OLDSTOCK2021-2022 SHEET.

thank you.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Add and subtract from another sheet - OzGrid Free Excel/VBA Help Forum
and Add and subtract from another sheet

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[maabadi]

It's not copying the NEWSTOCK value from TRANSACTION sheet when updating it into the Quantity stock in the OLDSTOCK2021-2022 SHEET.

You have macro at Transaction sheet that when add sale or retrival, it's automatically run and you see message box, if click No, formula works correctly, otherwise macro run and formula deleted.
Then Result is mistake.
A. If you want formula you should:
1. Disable this macro
2. Update range of your formula to your data range ( change all 13 & 7 to your data ranges last row at both sheet)
B. If you want macro, I should change your macro based Column K value as First Stock Value.
Please check and tell what you want exactly.

 

[Jasmin74]

I modified the code & it worked ,
here you go:

Option Explicit

Dim fo As Worksheet
Dim ln&, x!, s&

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Cells.CountLarge > 1 Then Exit Sub
    
    If Target.Row > 2 And Target.Column = 7 Then
        Application.EnableEvents = False
        Set fo = Sheets("OldStock2021-2022")
        If Range("B" & Target.Row) <> "" And Range("F" & Target.Row)

<> "" Then
            ln = WorksheetFunction.Match(Target.Offset(0, -5), fo.Range

("C:C"), 0)
            x = fo.Cells(ln, 5) 'Stok initial sur la feuille OldStock2021-2022
            Cells(Target.Row, 3) = fo.Range("D" & ln)       'Description
            Cells(Target.Row, 4) = fo.Range("G" & ln)       'Prix
            Cells(Target.Row, 5) = x                        'Stock initial
            s = IIf(Target.Offset(0, -1) = "sell", -1, 1)   'sens du mouvement

= 1 pour retour,-1 pour vente
            Cells(Target.Row, 9) = Target.Value * s + x     'Stock final
            fo.Range("E" & ln) = Target.Value * s + x       'Nouveau stock

mis à jour
            Range("A" & Target.Row) = Date                  'ou = Now si on

veut l'horodate
        Else
            MsgBox "Saisies incomplètes.", 16
            Exit Sub
        End If
    End If
    Application.EnableEvents = True
End Sub


Sub Evenement()
        Application.EnableEvents = True
End Sub

 

[maabadi]

Glad you can sort it and thanks for follow-up.