Copy Cells to specific rows in another sheet

SpasticUnicorns

New Member
Joined
Jan 15, 2014
Messages
6
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?
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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

New Member
Joined
Jan 15, 2014
Messages
6
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

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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

New Member
Joined
Jan 15, 2014
Messages
6

ADVERTISEMENT

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.


Code:
' 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

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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

New Member
Joined
Jan 15, 2014
Messages
6
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

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,539
Messages
5,596,754
Members
414,097
Latest member
FaeFen

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
Top