PriceList with qty order to copy specific cells to other sheet "PO"

[Knicknock]

Know similar things have been asked 10000s of times but I've literally been spending days trying to figure this out to work with my workbook - very new to using excel with VBA.

Basically I have a "PriceList" sheet that shows different products, color if applicable, size if applicable, qty pricing, and a qty column (Q) for how many to order. If the user inputs a value into Q, column T displays the product name, column U displays the product's color, column V displays the product's size, and column W displays the product's price (which depends on the qty ordered). All of these display along the same row with the first product starting at T9 (given a value has been entered into Q9). These columns (T / U / V / W) aren't visible to the user. The sheet extends to row291 but I'd like the macro to go further for when future changes are made to the price list that make it larger.

I then have a "PO" sheet - at first I was linking each cell in "PriceList!Q9":"PriceList!Q440" to "PO!B26":"PO!B457", "PriceList!T9":"PriceList!T440" to "PO!D26":"PO!D457", so-on-so-forth for U, V, and W. Then I had a button-click macro that refreshed the PO product range (rows 26 to 457) - it searched for any blank cells from B26:B457 and made the row hidden. The only problem with that is it took too long and I know this can be done better.

I'd prefer if I was able to loop a macro that copied all non-" " cells in PriceList!Q9:Q440 and inserted them into PO!B26:B457 (pricelist!t to po!d, pricelist!U to po!f, pricelist!V to po!h, and pricelist!w to po!i).

A couple things,
- some products will display a color, or a size, or both, or neither
- PO!B is merged with C, D is merged with E, F with G
- There is a subtotal row at the bottom of the PO that needs to expand down for when more products are added, or move up if product is later removed. I currently have the PO go from rows 26-34, with the subtotal on row 35. Once 10 products are added, the subtotal row would need to move down, if product is removed the subtotal would need to move up, up to row 35 again.

Thanks for anyone's help, I'm literally going nuts. I've spent easily three full days trying to make other peoples codes work with mine but I just have the experience to do it.

[Knicknock]

This is probably a better way to say this

See attached for workbook example

Basically some sort of trigger event that -

-whenever E1:E250 is changed from 0,
-copy cell and all non empty cells to it's right (E/F/G/H)
-paste copied cells to fill the table in sheet2
-when table is full, start inserting data so subtotal row expands
-If E1:E250 is changed back to 0, remove respective inserted row

[Knicknock]

Sorry for keeping this alive while I'm the only one posting but can't get this out of my head and need to get it finished

What I'm thinking of now is if it's possible to write a small code for each cell in range B9:B400 where if cells value is changed it triggers a macro for that specific row to hide or unhide

This isn't anywhere being correct but to give an idea:

Please Login or Register  to view this content.

Basically the worksheet is defaulted with each row within b9:b400 as hidden, have b401 the last row in the populated-PO, then B402 as the subtotal (that way b401 will always be visible as blank). When a target cell within that range is changed so it's value is greater than 0, show row, if it's value is still "", row hidden.