bring data from sheet to another based on cell value


  • Thanks to Mumps ... who took the time to write the whole Autofilter macro for you ... :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Carim

    Thank you for the kind words. You are always a gentleman. :)


    leap-out

    The problem was being caused by the fact that CUS1009 does not exist for INV10002. Try this version of the macro.


    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • I thought just fill in e5 but I have to write in e5, e8 to show the result yes it works very well but i don't want every time i write in e5,e8 copy to the bottom repeatedly I want delete old data in sheet "invoice" when i search a new data and if there is no match data also clear the cells

  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Try the attached file. It works properly for me.

    Files

    • INV1.xlsm

      (37.65 kB, downloaded 10 times, last: )

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • When you want to search based only on E5, first delete any value in E8 so that E8 is blank and then enter a value in E5.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • astonishing just I try doing last thing , but I failed I added this line to show the customer in e8

    but not work exactly it shows two values in e8 and e9

    Code
    1. .Range("c2", .Range("c" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy Range("e8")
  • I'm not sure what you mean. The macro works in the following way:


    -If E8 is not blank, when you change the value in E5, the macro will return only the rows from DATA that contain the E5 Invoice # in column B and the E8 ID in column C.

    -If E8 is blank, when you change the value in E5, the macro will return all the rows from DATA that contain the E5 Invoice # in column B regardless of the ID in column C.


    If this is not what you want, please explain in detail using a few examples form your data.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • first of all thanks for your time and feedback what I meant should e8 is empty , I do not fill it at all it fills automatically when I fill in e5 bring customer id and fill in e8 for instance if I only fill in e5 "1004 then automatically fill in e8 "CUS1012" and the rests of data from row 16

  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Brilliant ! awesome ! well-done ! what I can say you honestly you are legend , thanks is not enough for your right


    many thanks for your codes and time and patience and everything :thumbup:;):)


    best regards,


    leap-out