Copy data from one sheet to another

In the attached file '2020 cc.com formulas 022819.xlsm', which already has existing VBA code in it that I need to keep intact, I’m looking for help in adding additional code to achieve the following:

In ‘Sheet1’, column A is entitled ‘Item #’ and lists different product ‘items’ & pricing info. For each item listed, there are thirteen rows containing all the various pricing details for that item; the item # is repeated for all thirteen rows. The 1st row per item is a ‘quantity break’ (designated by column headers of ‘Q1’, ‘Q2’, ‘Q3’, etc.). The 3rd row per item lists a price for each quantity break; THESE are the target areas where I need new data from the worksheet called ‘Kathy’ to go.

In the ‘Kathy’ sheet, column A is also ‘item #’s’ that for the most part correspond with the item #’s listed in ‘Sheet1’. (Not all items in ‘Sheet1’ are listed in ‘Kathy’; I provide special instruction for data to flow through to those target areas below under “For Items Not Listed in ‘Kathy’” section below.) The same ‘quantity break’ values per item exist in the ‘Kathy’ sheet as are in ‘Sheet1’. In the cell immediately below the quantity break in ‘Kathy’ sheet is the price. For instance, the first item listed in ‘Kathy’ is ‘A1’, as signified in column A of the seven pricing detail rows for that item; the price for 50 pcs is listed as 9.81. This is the data to copy into the target areas of ‘Sheet1’.

So for any item listed in both sheets, copy the price per quantity break from ‘Kathy’ sheet and paste it into ‘Sheet1’ as defined in paragraph 2 above. I attach some screenshots showing what I am looking to have done:

‘Sheet1_A.jpg’ shows quantity breaks of 50, 100, 250, 500, etc. in the 1st row, starting at Column F. Two records below that is the corresponding current ‘price’, which are the target areas for the data that I need to change.

‘Kathy.jpg’ shows same quantity breaks but in different layout with the new price immediately below the qty break. These prices are what I want placed in the price fields in Sheet1.

‘Sheet1_B.jpg’ shows the new prices that I want the code to be able to perform for the item ‘A1’.

For Items Not Listed In ‘Kathy’: Insert ‘NoPrice’ in the target areas for that item.

(Currently, most of the pricing in both worksheets is the same; I need the code to be such that when we update the pricing in ‘Kathy’, it will flow through to the ‘Sheet1’ target areas after running the code again. Also, there may be additional items that we will want to add to either sheets, so we’ll need the code to account for that too if possible.)

Thanks for any help with this!

John Kilday

FYI, the top screenshot is 'Sheet1_A.jpg', middle one is 'Kathy.jpg', bottom one is 'Sheet1_B.jpg'.

Hoping someone can help with this. I'm interested in the 'Commercial Services' option if not, but I haven't been able to figure that out. I sent question on that to admin as well but no reply...

Cross-posted replica of this post here due to lack of activity:

https://www.ozgrid.com/forum/forum/h...eet-to-another