Purchase Order Generator

[Barracuda72]

Afternoon... I have created a Purchase Order Generator, and now I need to log the entries on the purchase order to another sheet before I clear the purchase order for new data. I can get it to update and copy to the log, but it keeps over writing what I did rather than creating a new row and new entry point.

 

[Joe4]

Welcome to the Board!

Post the VBA code you have that writes the log entry, and we can probably help you adjust it so it doesn't keep writing over the same row.

 

[Barracuda72]

I just figured it out, so I am still trying to write it... But here is the beginning of what I am writing. I need the cells to copy to the log with an update button, which I have figured out and am applying the below unfinished code or macro into it... I think, anyway. Lol...
Sub UpdateLog()
Range("RequestedBy").Copy Range("RequestedBy2")
Range("POnumber").Copy Range("POnumber2")
End Sub

 

[Joe4]

How are your ranges being defined?
If the values never change, you will continually overwrite the destination. So you may not be able to use named range there.
You may need to go to the sheet, and select the next available row.

 

[Barracuda72]

Here is the code I have written in the rest of the Generator... Maybe this will help as well. I have no idea.
Private Sub AddItem_Click()


'Turn off Screen Updating so updating of PO cannot be seen until finished.
Application.ScreenUpdating = False


'Declare relevant variables
Dim CurrentProduct As String
Dim ProductID As String
Dim UnitPrice As Currency
Dim Vendor As String
Dim Quantity As Integer
Dim LineItemTotal As Integer
Dim POrowstart As Integer


'Information regarding which row to start with for PO
LineItemTotal = Range("LineItemTotal").Value
POrowstart = 10






'Get current product selection information from the 'ProductSelection' UserForm
CurrentProduct = ProductList.Value
Quantity = QuantityBox.Value


'Lookup related product information from the ProductListing range
ProductID = Application.WorksheetFunction.VLookup(CurrentProduct, Range("ProductListing"), 2, False)
Vendor = Application.WorksheetFunction.VLookup(CurrentProduct, Range("ProductListing"), 3, False)
UnitPrice = Application.WorksheetFunction.VLookup(CurrentProduct, Range("ProductListing"), 4, False)


'Populate next line item with product selection
Range("B" & POrowstart + LineItemTotal).Value = Quantity
Range("C" & POrowstart + LineItemTotal).Value = ProductID
Range("D" & POrowstart + LineItemTotal).Value = CurrentProduct
Range("M" & POrowstart + LineItemTotal).Value = UnitPrice
Range("L" & POrowstart + LineItemTotal).Value = Vendor


' Reset Userform values to indicate item was added to PO
QuantityBox.Value = ""
labelProductName.Caption = "Product Name: "
labelProductID.Caption = "Product ID: "
labelVendor.Caption = "Vendor: "
labelUnitPrice.Caption = "Unit Price:"


'Since PO only has 5 line items, needs to end program if 5 have been selected. See homework for additional assignment for higher items.


If LineItemTotal = 29 Then
MsgBox "Your Purchase Order is complete."
Unload Me
End If




End Sub


Private Sub FinishOrder_Click()
'Exit Macro
Unload Me
End Sub


Private Sub labelProductCategory_Click()


End Sub


Private Sub ProductList_Click()
'This macro runs when an item in the Product Selection listbox is selected


'Declare relevant variables
Dim CurrentProduct As String
Dim ProductID As String
Dim UnitPrice As Currency
Dim Vendor As String




'Grab current product from ProductList ListBox selection
CurrentProduct = ProductList.Value


'Change Product Name label to reflect current item.
labelProductName.Caption = "Product Name: " & CurrentProduct


'Lookup Product ID based on Product Description and change label
ProductID = Application.WorksheetFunction.VLookup(CurrentProduct, Range("ProductListing"), 2, False)
labelProductID.Caption = "Product ID: " & ProductID


'Lookup Product Category based on Product Description and change label
Vendor = Application.WorksheetFunction.VLookup(CurrentProduct, Range("ProductListing"), 3, False)
labelVendor.Caption = "Vendor: " & Vendor


'Lookup Unit Price based on Product Description and change label
UnitPrice = Application.WorksheetFunction.VLookup(CurrentProduct, Range("ProductListing"), 4, False)
labelUnitPrice.Caption = "Unit Price: $" & UnitPrice


End Sub

 

[Barracuda72]

I can remove the names of some of the cells on the log but the cells in the Purchase order itself must remain in order for the codes there to continue working...

 

[Joe4]

I don't see anything in there showing how your named ranges are being set.
Are they already set up manually on your workbook?
If so, please let us know the sheet and range each one refers to.

 

[Barracuda72]

ooops...

 

[Barracuda72]

Think this one may show the code and macros working for you.

 

[Joe4]

I cannot download the file from my current location. I might be able to take a look at it when I am at a different location later tonight where I can download files.