USD $40 Copy data from one sheet to another

  • Transaction ID: 2JN60797XL075151M

    (This was originally posted at…one-sheet-to-another.html, but I let them know that I posted it here due to lack of response over there.)

    In the attached file '2020 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’, screenshot below, 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’, 1st screenshot in 2nd reply, 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’, 2nd screenshot in 2nd reply, 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, and I will be changing the actual values of 'Kathy' with new year pricing later, so we’ll need the code to account for that too if possible.)

    Thanks for any help with this!

    John Kilday

    File to add code to:
    2020 formulas 022819.xlsm

    [ATTACH=JSON]{"alt":"Click image for larger version Name:\tSheet1_A.JPG Views:\t1 Size:\t49.9 KB ID:\t1215684","data-align":"none","data-attachmentid":"1215684","data-size":"full"}[/ATTACH]

  • Hi John,
    This should be ok.

    I just noticed that in sheet "Kathy", the values are usually ordered like this in column B: Prices / Quantity / Price1 / Price2 / Price3 / Price4 / DSC
    However for item A117 starting at row29, this is not the case. The quanty is in row35 instead of row 30.

    Does the macro have to consider those case?
    (it would be much simpler if you can guarantee that structure will always be correct, otherwise I can add a verification step)


  • One other thing I need to have the code do if possible is if an item from 'Kathy' is not found in 'Sheet1', or if we add a new item to 'Sheet1' that is not in 'Kathy', then highlight these 'Sheet1' items in some way so that we can easily identify them and manually configure the pricing for those.