How do you copy and paste something automatically? - Page 2
How do you copy and paste something automatically?
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: How do you copy and paste something automatically?

  1. #11
    Board Regular
    Join Date
    Mar 2002
    Posts
    122
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #12
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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,

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #13
    Board Regular
    Join Date
    Mar 2002
    Posts
    122
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  4. #14
    Board Regular
    Join Date
    Feb 2002
    Location
    Georgia USA
    Posts
    569
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    copy the code, right click on your stock sheet, view code, paste it in the window that opens.

  5. #15
    Board Regular
    Join Date
    Mar 2002
    Posts
    122
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ok will do, thanks!

  6. #16
    Board Regular
    Join Date
    Mar 2002
    Posts
    122
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #17
    Board Regular
    Join Date
    Mar 2002
    Posts
    122
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!!!!

  8. #18
    Board Regular
    Join Date
    Mar 2002
    Posts
    122
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  9. #19
    Board Regular
    Join Date
    Mar 2002
    Posts
    122
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ...

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com