Sami, let's try this code for starters:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim ReOrder_Name(), ReOrder_Price(), ReOrder_Qty()
Dim ReOrder_Supplier()
Dim i, Counter As Long
i = 0
For Each c In Range("D2", Range("D65536").End(xlUp).Address)
If c.Value <= 10 Then
i = i + 1
ReDim Preserve ReOrder_Name(i)
ReDim Preserve ReOrder_Price(i)
ReDim Preserve ReOrder_Qty(i)
ReDim Preserve ReOrder_Supplier(i)
ReOrder_Name(i) = Range("A" & c.Row).Value
ReOrder_Price(i) = Range("C" & c.Row).Value
ReOrder_Qty(i) = Range("G" & c.Row).Value
ReOrder_Supplier(i) = Range("F" & c.Row).Value
End If
Next c
If i <> 0 Then
For Counter = 1 To i
Sheets("ReOrderSheet").Range("A65536").End(xlUp).Offset(1, 0). _
Value = ReOrder_Name(Counter)
Sheets("ReOrderSheet").Range("B65536").End(xlUp).Offset(1, 0). _
Value = ReOrder_Price(Counter)
Sheets("ReOrderSheet").Range("C65536").End(xlUp).Offset(1, 0). _
Value = ReOrder_Qty(Counter)
Sheets("ReOrderSheet").Range("E65536").End(xlUp).Offset(1, 0). _
Value = ReOrder_Supplier(Counter)
Sheets("ReOrderSheet").Range("D65536").End(xlUp).Offset(1, 0) _
.FormulaR1C1 = "=RC[-2]*RC[-1]"
Next Counter
End If
End Sub
Here's my assumptions:
• The data in your stock sheet is laid out as follows: A-Product Name, B-Product ID, C-Price, D-Quantity on Hand, E-Total Value of Quantity on Hand (C*D), F-Supplier, G-Re-Order Quantity
• Your Re-Order worksheet is named "ReOrderSheet"
• The data in your Re-Order sheet is laid out as follows: A-Product Name, B-Price, C-Quantity Required (Re-Order Quantity), D-Cost (C*B), E-Supplier
Give it a try and let me know how you work out.
Regards,