Inventory Tracker

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim int_range As Range
Set int_range = Intersect(Target, Range("F2:F" & Rows.Count)) 'Colonne nombre de produits
"F"
Dim prod_range As Range
Set prod_range = Intersect(Target, Range("C2:C" & Rows.Count)) 'Colonne noms produits "C"
Dim i As Integer
i = Target.Row - 1 '3 est la premiere colonne du nombre de produits
If Not (prod_range Is Nothing) Then
With ActiveSheet 'Nom de la feuille "Sheet1"
If (Target.Rows.Count <> 1 Or Target.Columns.Count <> 1) Then Exit Sub
For Each c In prod_range
c.Offset(-1 * i, (5 * i) + 7).Value = "Produit (Unité)"
c.Offset(-1 * i, (5 * i) + 7).Columns.AutoFit
c.Offset(-1 * i, (5 * i) + 8).Value = "Nb total"
c.Offset(-1 * i, (5 * i) + 8).Columns.AutoFit
c.Offset(-1 * i, (5 * i) + 9).Value = "Suivi"
c.Offset(-1 * i, (5 * i) + 9).Columns.AutoFit
c.Offset(-1 * i, (5 * i) + 10).Value = "Date et Heure du suivi"
c.Offset(-1 * i, (5 * i) + 10).Columns.AutoFit
c.Offset(-1 * i, (5 * i) + 11).Value = "Modificateur"
c.Offset(-1 * i, (5 * i) + 11).Columns.AutoFit
Next
End With
End If
If Not (int_range Is Nothing) Then
With ActiveSheet 'Nom de la feuille "Sheet1"
If Target.Count > 1 Then Exit Sub
For Each c In int_range
If Not IsNumeric(c.Value) Then
MsgBox "Veuillez uniquement entrer des chiffres!"
c.Value = vbNullString
Else
c.Offset(-1 * (i - 1), (5 * i) + 5).Value = c.Offset(-1 * (i - 1), (5 * i) + 5).Value + c.Value
.Range("O" & Rows.Count).Offset(0, 5 * (i - 1)).End(xlUp).Offset(1).Value = .Range("C" &
Target.Row).Value
'"O" suivi du nom de produits "C"
End If
Next
.Range("Q" & Rows.Count).Offset(0, 5 * (i - 1)).End(xlUp).Offset(1).Value = .Range("F" &
Target.Row).Value
'"Q" suivi du nombre de commandes "F"
End With
End If
Dim n As Long
Const suivi_column As Integer = 17
For n = suivi_column To ActiveSheet.UsedRange.Columns.Count Step 5
If (n - 2) Mod 5 = 0 Then
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Columns(n)) Is Nothing Then
If Target.Value <> vbNullString Then
Target.Offset(0, -2).EntireColumn.AutoFit
Target.Offset(0, 1).Value = Format(Now(), "yyyy-MM-dd | hh:mm")
Target.Offset(0, 1).ColumnWidth = 20
Target.Offset(0, 2).Value = Application.UserName
Target.Offset(0, 2).EntireColumn.AutoFit
End If
End If
End If
Next n
End Sub
Sub Backup_Mensuel()
Dim myWorksheet As Worksheet
Dim myWorksheetName As String
myWorksheetName = "BKP " & Format(Now, "mmmm_yyyy")
For Each myWorksheet In Worksheets
If myWorksheet.Name = myWorksheetName Then
MsgBox "La feuille du mois existe déjà!"
Exit Sub
End If
Next myWorksheet
Sheets.Add.Name = myWorksheetName
Sheets(myWorksheetName).Move After:=Sheets(Sheets.Count)
Worksheets("SUIVI DES COMMANDES").Range("O1", Cells(1,
Columns.Count).End(xlToLeft)).Copy Worksheets(myWorksheetName).Range("A1")
Worksheets("SUIVI DES COMMANDES").Range("O2",
Range("O2").SpecialCells(xlCellTypeLastCell)).Cut
Worksheets(myWorksheetName).Range("A2")
'backup à partir de la colonne O
End Sub