How do you copy and paste something automatically?

Sami

Board Regular
Joined
Mar 4, 2002
Messages
122
Ive got a stock worksheet and a reorder sheet. Im using conditional formatting so that when any stock goes below 10 quantity then it goes red. But how do i make the products that go red get copied automatically and then pasted into the reorder sheet? lil help plz!
 
to q 1 i could. and q2 ill have just a few for the testing but then there b as many rows as there are products.
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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,
 
Upvote 0
Thank u. where do i put this code? do i have to make a macro for it or can i put it somewhere so it does it automatically?
 
Upvote 0
copy the code, right click on your stock sheet, view code, paste it in the window that opens.
 
Upvote 0
it says on the code "compile error" on the "End if" statement????
which value is excel taking ie which quanity when it goes below a certain point will it copy and paste.
Thanks
 
Upvote 0
hey i got it to work! its brialliant! but....
when i enter any quantity it copys and pastes it even if its 1000 which doesnt need ordering. how can i program a value so that when it reaches that value or below- then - it will copy n paste like it is now?
thanks a lot 4 ur help!!!!
 
Upvote 0
it seems to be entering the data twice each time and under the final cost of the last product that needs to be ordered there are loads of zeros 0.
how do i fix this?
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top