Capture and track changes

[TG2812]

Sorry but I have not got an access to these platforms. I will attempt to express the requirements herebelow.
Basically what I'm trying to attempt can be summarized in the below chart:

[TABLE="class: cms_table, width: 738"]
<tbody>[TR]
[TD]Reference[/TD]
[TD]Country[/TD]
[TD]Product Range[/TD]
[TD]Warehouse[/TD]
[TD]Production month[/TD]
[TD]Previous Quantity[/TD]
[TD]New Quantity[/TD]
[TD]Changes[/TD]
[TD]Balance[/TD]
[/TR]
[TR]
[TD]Peanut134SAZ[/TD]
[TD]Germany[/TD]
[TD]Food[/TD]
[TD]Cologne[/TD]
[TD="align: right"]Mar-19[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Butter23[/TD]
[TD]Germany[/TD]
[TD]Food[/TD]
[TD]Cologne[/TD]
[TD="align: right"]Mar-19[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Toast98YT[/TD]
[TD]Germany[/TD]
[TD]Food[/TD]
[TD]Cologne[/TD]
[TD="align: right"]Mar-19[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Pasta34ZSW[/TD]
[TD]UK[/TD]
[TD]Food[/TD]
[TD]London[/TD]
[TD="align: right"]Apr-19[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]


Requirements;

#1: The remaining balance for a given country, product range, warehouse and production month always need to be equal to 0.
Example: I increase my production of Peanut134SAZ by 4 in March. I need to reduce the Butter23 by 2 and Toast98YT by 2. My remaining balance equals 0 as the surplus created by Butter134SAZ has been taken out from other references.

#2: The remaining balance for a given country, product range, warehouse and production month does not equal to 0.
Example: I reduce the production of Pasta34SW by 5 in April. I consequently need to increase my production on other products during April to bring my remaining balance down to 0. A message should appear to the user before closing workbook if remaining balance for a particular product is not equal to 0.Note:Production will be impacted in worksheet "Main". Changes will be listed under sheet "Tracking". If we could depart from the code pasted above, this would be great. I believe a few line of codes should be added but I'm not knowledgeable enough to carry on by my own.


Ideally once the tracking table is complete and the user finishes completing all necessary adjustments, it would be nice to this table via Outlook (just the email window with table copied).Thanks a lot in advance for your help.

 

[TG2812]

This is the VBA code I came up with so far and that would require some adjustments with above requirements. Being beginner in VBA i need help.


'Global variables
Dim oldAddress As String
Dim oldValue As String



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo err
Dim sSheetName As String
sSheetName = "Main"


If ActiveSheet.name <> Tracking Then 'need to capture that occur in sheet "Main" only -> how can i adjust the code?
Application.EnableEvents = False




Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Range("A" & Target.Row).Value
Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Range("B" & Target.Row).Value
Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Range("C" & Target.Row).Value
Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Range("D" & Target.Row).Value
Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = oldValue
Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 5).Value = Target.Value
Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 6).Value = Environ("username")
Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 7).Value = Now
Sheets("Tracking").Hyperlinks.Add anchor:=Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 8), Address:="", SubAddress:="'" & sSheetName & "'!" & oldAddress, TextToDisplay:=oldAddress
End If




err:
Application.EnableEvents = True
End Sub





Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo err
oldValue = Target.Value
oldAddress = Target.Address


err:
End Sub

 

[TG2812]

Any idea to the adjustments i have to make to the code? Any help would be greatly appreciated.
Thank you all in advance.