Announcement

Collapse
No announcement yet.

USD $40 Copy data from one sheet to another

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • USD $40 Copy data from one sheet to another



    Transaction ID: 2JN60797XL075151M

    (This was originally posted at https://www.excelforum.com/excel-pro...o-another.html, but I let them know that I posted it here due to lack of response over there.)

    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’, 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 cc.com formulas 022819.xlsm

    Sheet1_A.jpg:
    Click image for larger version  Name:	Sheet1_A.JPG Views:	1 Size:	49.9 KB ID:	1215684
    Attached Files

  • #2
    Kathy.jpg:
    Click image for larger version

Name:	Kathy.JPG
Views:	1
Size:	32.0 KB
ID:	1215686

    Sheet1_B.jpg:
    Click image for larger version

Name:	Sheet1_B.JPG
Views:	1
Size:	47.2 KB
ID:	1215687

    Comment


    • #3
      Hello,

      I will look into this and let you know if I have any questions.

      Comment


      • #4
        Thank you

        Comment


        • #5
          Hi John,
          Explanations are clear and I have a good idea of the required solution.
          When would you need this?

          Comment


          • #6
            Ok GC, great to hear that. If possible, I'd like to get it by tomorrow afternoon, like 3 p.m. Eastern if that works?

            Comment


            • #7
              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)

              Thanks,

              Comment


              • #8
                Hello again,

                I have solution ready assuming that data in tab Kathy is always in correct order.
                I can send you payment detail via PM and could make minor modification of the code if required.

                Comment


                • #9
                  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.

                  Comment


                  • #10
                    Hi,

                    1) see attached image with item A117. Note that I was not able to download the file from Ozgrid forum, so I used the one you posted on ExcelForum. Maybe it was different.
                    2) I'll modify the code to highlight the item

                    Comment


                    • #11


                      File sent via PM.

                      Comment

                      Working...
                      X