عمل تأكيد لنقل معلومات من ورقة لأخرى

[Jasmin]

مرحبا إخوتي
عندي كلفي به ورقتين
ورقة 1- Oldstock2021-2022
وبها معلومات المستودع

ورقة 2- Transaction
عنها المعلومات التي أخذناها من الورقة الأولى 
وعندنا عمليتين : بيع وإسترجاع والكمية Quantity
بعد ان أضع الكمية المباعة أريد عمل تأكيد لكي أنقل
الNewStock من هذه الورقة للورقة الأولى Quantity
in stock

2- كيف أضيف الdate تلقائيا كلما أضفت سطر جديد
أنا وضعته  now()  بس كيف يتم كتابته تلقائياً

وشكرا لكم.

test-0-.xlsx

[lionheart]

Sheet1 should be unprotected to let the code modify the quantity

  In the following code replace this word ÈíÚ with the Arabic equivalent

This is worksheet module (Transaction worksheet)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim x
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Target.Row > 2 And Target.Column = 7 Then
        Application.EnableEvents = False
            x = Application.Match(Target.Offset(, -5).Value, Sheets(1).Columns(3), 0)
            If Not IsError(x) And Target.Offset(, -1).Value = "ÈíÚ" Then
                If MsgBox("New Stock Will Be Updated And This Row Will Be Delete. If OK Click 'Yes'", vbYesNo + vbQuestion) = vbYes Then
                    Sheets(1).Cells(x, 5).Value = Sheets(1).Cells(x, 5).Value - Target.Value
                    Target.EntireRow.Delete
                End If
            End If
        Application.EnableEvents = True
    End If
End Sub

 

[Jasmin]

شكرا لكم .هذا هو الملف مفتوحا لو تكرمتم،ولكن أنا لا أريد لأحد أن يعدل عليه يدويا.
 .. وأيضا ممكن فكرة عن السؤال الثاني؟Where should i put this code brother
Thank you in advance

test-0-opened.xlsx

[lionheart]

Right-click on the worksheet named Transactions and click on View Code to paste the code in the worksheet module and as for the second point not clear for me at all

[Jasmin]

أخي
أنا أريد الـ Newstock بالtransaction تنتقل عال Quantity in Stock بالورقة OldStock2021-2022
انا نقلت الكود كما قلت

بالنسبة للنقطة الثانية
كيف ممكن أن يتمّ إضافة تاريخ اليوم عند إضافة كل سطر جديد 
مثلا ترى انه لا يوجد تاريخ في السطر الثاني

 

 

test-0-opened.xlsx

[Jasmin]

وضعت الكود أخي ولم يعمل شيء
the quatity in sheet1 was not updated- any tips?

test-0-fr-m.xlsm

[lionheart]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim x
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Target.Row > 2 And Target.Column = 7 Then
        Application.EnableEvents = False
            x = Application.Match(Target.Offset(, -5).Value, Sheets(1).Columns(3), 0)
            If Not IsError(x) And Target.Offset(, -1).Value = "sale" Then
                If MsgBox("New Stock Will Be Updated And This Row Will Be Delete. If OK Click 'Yes'", vbYesNo + vbQuestion) = vbYes Then
                    Sheets(1).Cells(x, 5).Value = Sheets(1).Cells(x, 5).Value - Target.Value
                    Target.EntireRow.Delete
                End If
            End If
        Application.EnableEvents = True
    End If
End Sub

Change the quantity in column G to trigger the code

[Jasmin]

brother

why are you deleting the last row??

[lionheart]

If you don't want to delete the row, just comment the line

Target.EntireRow.Delete

 

[Jasmin]

Thank you very much brother
2 notes please 

1- I need to update the 1st sheet from the column NEWSTOCK (because the operation might be + or - as you can see the difference between the two lines :

 1st was sale & 2nd was retrieval so the final new stock is the reference) Because you only made the code for sale.

2- the 3rd row didn't update the 1st sheet until i rewrite the quantity again !! so it was registered -4 - though it was 2

Notice the quantity was 191 in one row it became 187! 
coz i write 2 twice coz it didn't update the 1st sheet  1st time though i clicked on yes ..

pls add 2 rows & try it for the 2 types of transaction.

I appreciate your help very much, may God reward you.

 test-0-fr-m.xlsm

[lionheart]

Dim tmp

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim x
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Target.Row > 2 And Target.Column = 7 Then
        Application.EnableEvents = False
            x = Application.Match(Target.Offset(, -5).Value, Sheets(1).Columns(3), 0)
            If Not IsError(x) Then
                If MsgBox("New Stock Will Be Updated . If OK Click 'Yes'", vbYesNo + vbQuestion) = vbYes Then
                    Sheets(1).Cells(x, 11).Value = Sheets(1).Cells(x, 5).Value
                    Sheets(1).Cells(x, 5).Value = Target.Offset(, 2).Value
                    Cells(Target.Row, 1).Value = Format(Date & Space(1) & Time, "dd/mm/yyyy hh:mm")
                Else
                    If Not IsEmpty(tmp) Then Target.Value = tmp
                End If
            End If
        Application.EnableEvents = True
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Target.Row > 2 And Target.Column = 7 Then tmp = Target.Value
End Sub

 

[Jasmin]

Thank you brother 

the date worked just fine

But i ve tried the first row as attached - when you update the stock in the 1dt sheet, too bad the second one change too - so obviously it's not correct.

test-0-fr-m.xlsm

[lionheart]

What do you mean by the second one

Can you use images to explain your problem as I am confused about the problem

[Jasmin]

Here you go,
though i ve sent the sheet 
 

[lionheart]

Look you have to study the code well so as to be able to modify and to learn something new (at least the basics)

If I got what you meant, just comment this line out

Sheets(1).Cells(x, 11).Value = Sheets(1).Cells(x, 5).Value

 

[Jasmin]

I am sorry, I am a very newbie
I will try to learn, but now I don't have time

(btw, to comment it do I put //)

Well sir

I removed this line, but still same result, it's updating the current line.

[lionheart]

If you removed the line I referred to, the code will not update or even touch the column K in the first sheet

To put comment, you can use apostrophe (') at the beginning of the line or using the word rem

[Jasmin]

this is the code I have
I deleted the line & saved the file & retried
Same result
 

Dim tmp

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim x
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Target.Row > 2 And Target.Column = 7 Then
        Application.EnableEvents = False
            x = Application.Match(Target.Offset(, -5).Value, Sheets(1).Columns(3), 0)
            If Not IsError(x) Then
                If MsgBox("New Stock Will Be Updated . If OK Click 'Yes'", vbYesNo + vbQuestion) = vbYes Then
                    
                    Sheets(1).Cells(x, 5).Value = Target.Offset(, 2).Value
                    Cells(Target.Row, 1).Value = Format(Date & Space(1) & Time, "dd/mm/yyyy hh:mm")
                Else
                    If Not IsEmpty(tmp) Then Target.Value = tmp
                End If
            End If
        Application.EnableEvents = True
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Target.Row > 2 And Target.Column = 7 Then tmp = Target.Value
End Sub

 

[lionheart]

. You have to explain in words the exact problem, use Arabic (I can get the Arabic well)

I will let other members share in the thread as I am confused and can't get what is the problem exactly

[Jasmin]

المطلوب هو كالتالي:
الورقة transaction تحتوي على معلومات البضائع من الورقة old transaction

المطلوب نقل الكمية NewStock من ورقة الTransaction  إلى ورقة الـOld transaction عامود E -Quantity in Stock- عند كل سطر وحركة بيع Sale  أو إرجاع Retrieval جديدة

مرفق الملف الأصلي دون كود .. شكرا سلفا

test-0-fr-a.xlsm

[lionheart]

This is exactly what the code did. Generally, we can wait for other members to share their ideas

God bless you

[Jasmin]

أرفق لكم الطريقة كود وملف لمن أراد أن يستفيد لاحقاً

وشكرا للجميع.

 

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

 

QuckSolution-1-.xlsm