If order amount specified on product line list, pull line information to purchase order

Hello.

I cannot figure out the formula/macro to do this. Apparently I do not deserve my Microsoft Office Specialist certification!

I have two sheets in an excel workbook. The first is a product line, the second is a purchase order. If I enter an amount needed to be ordered in the first column of the product line, Id like the information to automatically fill in on the purchase order.

I also need to be able add items to the product line in this workbook without it affecting the formula/macro, and to do the same thing throughout multiple other Excel workbooks. Much appreciated.

-JeffreyPurchase Order - Design Hardware (Template).xlsx

will you run this macro once for each workbook (lets say at the end of every day), or do you plan on doing it as you go(with each purchase that you enter to the first sheet?).

Originally Posted by Bishonen

will you run this macro once for each workbook (lets say at the end of every day), or do you plan on doing it as you go(with each purchase that you enter to the first sheet?).

Every time we purchase, I will open the template and "Save As" a new document. That way the original is always in tact.

Thanks! Works like a charm!

Originally Posted by JieJenn

put this in your worksheet event. I guess that makes me a newbie since I don't have a MS Office Specialist certification

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long, i As Long
Dim wsP As Worksheet
1 Application.ScreenUpdating = False

Set wsP = Worksheets("Product Line")

If Target.Column = 1 Then
    With wsP
    LR = .Cells(Rows.Count, 1).End(xlUp).Row
    If LR < 5 Then
        Application.ScreenUpdating = True
        Exit Sub
    End If

Worksheets("Purchase Order").Range("A5:F40").ClearContents
    For i = 5 To LR
        If .Cells(i, 1) > 0 Then
            With Worksheets("Purchase Order")
              
                .Cells(.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1) = wsP.Cells(i, 1)
                .Cells(.Cells(Rows.Count, 2).End(xlUp).Row + 1, 2) = wsP.Cells(i, 3)
                .Cells(.Cells(Rows.Count, 3).End(xlUp).Row + 1, 3) = wsP.Cells(i, 4)
                .Cells(.Cells(Rows.Count, 4).End(xlUp).Row + 1, 4) = wsP.Cells(i, 5)
                .Cells(.Cells(Rows.Count, 5).End(xlUp).Row + 1, 5) = wsP.Cells(i, 7)
                .Cells(.Cells(Rows.Count, 6).End(xlUp).Row + 1, 6) = .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 1) * .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 5)
            End With
        End If
    Next i
                

    End With
End If
Application.ScreenUpdating = True
End Sub

OK one more question... Now I am trying to add a simple sum formula to the Purchase Order to get total cost of goods being ordered. I also need to add freight charges, etc. in the space below the purchase order. Every time I enter it and go back to the product line, the formulas and text disappear. Help? I am no good with macros....

Originally Posted by JieJenn

Post a sample file with description.

Any luck? Still trying to mess around with it to no avail....

Purchase Order - Design Hardware (Template).xlsm

With macro in place throughout outlined cells, I need to be able to make changes below on the purchase order. They disappear whenever I go to product line and change "To Order" quantities.