Copy Cells to specific rows in another sheet

[SpasticUnicorns]

Hi all,

I've been trying to find some info on the best way to do this but I can't really find anything. I currently have created a sheet that if we input a part number it will populate cells with the price, description and relevant pat number for each supplier. I want to be able to add a button for each supplier that can open our purchase order sheet, take the values of the cells in price, description etc and insert them into the relevant row of the PO. If the first row is filled then go to the next and so forth. Is this even possible?

 

[L. Howard]

Sound doable, but very short on info, layout and such.

Maybe post a link to your sample workbook along with what you want to happen and where, being very specific.

I use Drop Box but there are others also.

Regards,
Howard

 

[SpasticUnicorns]

Sorry about that, I wasn't even sure if it was achievable so didn't think of the extra info. This is the 2 sheets in question.. https://www.dropbox.com/s/j7ddh52u9vt1j07/PO and Part Sheet.7z

I also can't put them into the same workbook as there are others that use the PO that we don't want to access the parts sheet. Any help woudl be greatly appreciated. Oh and please ignore the mess that is in the PO vba, I'm learning lol.

 

[L. Howard]

I was unable to bring your two sheets up in Excel, have not seen that format and don't know how to operate it.

Howard

 

[SpasticUnicorns]

Ah sorry about that, I didn't realise it went to a 7z and not zip format for the 2 sheets. This one should be able to be opened in windows.

https://www.dropbox.com/s/9xdqpcwrpt9oj91/Sheet.zip

 

[L. Howard]

Okay, got them open.

These are two separate workbooks not two sheets within a workbook.

So what is supposed to happen? There is no data anywhere. What would I do step by step to achieve what you want?

Howard

 

[SpasticUnicorns]

Oh ok because I'm currently building/playing I haven't added instructions into the whole thing yet and I haven't added a quantity cell or the button to the parts comparison sheet then either. I incorrectly said sheets when I meant 2 workbooks but they can't be joined as other's shouldn't access the Parts sheet. You can try a part number with 888640, that will populate the fields.

I have added a quantity cell (H22) and a button so when I click the button it opens the PO and copies data (M10, M13, H22, M16)to the relevant fields (A29:D29). Then I can save and print the PO. I have this code not in the examples I uploaded which opens the PO but gives a 400 error.

' Open workbook if not already open
Sub WorkbookTest()
Dim WB As Workbook


On Error Resume Next
Set WB = Workbooks("Order Form Template.xls")


If Err <> 0 Then
    On Error GoTo 0
    Workbooks.Open ("Z:\Orders\Template\Order Form Template.xls")
End If


' Copy and paste cells to specific location on another sheet


Dim LR As Long, i As Long, cls
cls = Array("M10", "M13", "H22", "M16")
With Sheets("ORDER FORM")
    LR = WorksheetFunction.Max(29, .Range("A" & Rows.Count).End(xlUp).Row + 1)
    For i = LBound(cls) To UBound(cls)
        .Cells(LR, i + 1).Value = Me.Range(cls(i)).Value
    Next i
End With
End Sub

 

[L. Howard]

I did find the data to the right, on the comparison sheet.

I entered one of the part numbers in the comparison box and got some info from each supplier.

I guess you want to have the option of selecting either supplier's data and click a button and that info will be transferred to the PO workbook, with the next selection on the next line on the PO?

If that is true then it would be cleaner and easier if both sheet were in the same workbook, is that possible?

Howard

 

[SpasticUnicorns]

One button for each supplier would be fine it's really only cosmetic and there are only 2 of us using it anyway. My boss would prefer if I didn't add it to the PO sheet as every employee has access to it but he doesn't want them to be able to access pricing etc, only us copier techs. If it has to be that way though I can explain to him that's its it or bust.

 

[L. Howard]

A few hurdles to get over if acceptable to you.

Check out the two links, Order and Price.

I have revamped the Price Comparison because of the numerous merged cells and heavy formatted on the sheet. I copied the data base as is and reworked the entry cells for the part numbers etc.

The formulas are in the new cells but do not work. I need you to adjust the formulas to produce proper returns to each cell. You, of course can rearrange to suit if you feel compelled.

I did just a little revamp on the PO sheet, again removing merged cells. (Column C width = 0)

If you insist on merged cell to the extent you had them on the original, then I will have to bow out. I have neither the patience nor the skill to deal with them effectively. Virtually all the Excel Pros strongly advise against merged cells anywhere that formula, functions or VBA code will be used.

Merged cells are okay for make pretty on the sheet outside of the "working" cells. Troubleshooting can become a nightmare.

The massive formatting is also burdensome to the sheet, ie. the black out and I noticed most cells on Price Comp. sheet will not show what is entered in them, don't know what that is, some more formatting I am guessing.

Beyond that the copy of data from one workbook to another is not that complicated.

https://www.dropbox.com/s/3odr8v6yhgs7vry/ORDER FORM TEST SHEET Drop Box.xlsm

https://www.dropbox.com/s/2mvimzoyhsyx4f5/PRICE COMPARE TEST SHEET Drop Box.xlsm

Howard