+ Reply to Thread
Results 1 to 7 of 7

DropBox value triggering copypaste by "SEARCHING" for the correct cells to copypaste

  1. #1
    Forum Contributor
    Join Date
    09-20-2012
    Location
    Norge
    MS-Off Ver
    Excel 2007
    Posts
    114

    DropBox value triggering copypaste by "SEARCHING" for the correct cells to copypaste

    Hello,

    How can one accomplish to have a DropBox With a list of, lets say 50 items and Next to it an empty field (for example C13:K41) and for each item that is chosen in the DropBox there should be copy pasted the corresponding line (from the data area lets say JW1:KE50) With information into the first empty row within C13:K41.

    In a row in the data area JW1:KE50 for example JW1:KE50 the cell JV1 contains one of the values from the DropBox list. And the DropBox list is mentioned in AA1:AA50.


    Now I know one way of doing this With the following code, but writing this for each and every possible value is huge job. (The actual data amount in question is about 1000):

    Please Login or Register  to view this content.
    Anybody know how to do this a lot smarter?


    The Logic I´m thinking about is something like If the DropBox value is Equal one of the listed items in the range JV1:JV50 Then OFFSET Row 1, 2, 3, 4, 5, 6, 7, 8 and 9 and then copypaste these into first available row in the C13:K13 field.


    Anybody know a smart code for this so that it will search for the correct cells to copy paste instead of having me writing more than 1000 of the same code over and over again?


    Thanx in advance

  2. #2
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: DropBox value triggering copypaste by "SEARCHING" for the correct cells to copypaste

    Is it correct to assume that if you'd choose the value that connects to AA14 that the equivalent of that would be in JV14? Or it might be somewhere else?

    How about something like this :
    Please Login or Register  to view this content.
    If this is not what you're looking for, please upload a sample workbook.
    Please click the * below if this helps
    Please click the * below if this helps

  3. #3
    Forum Contributor
    Join Date
    09-20-2012
    Location
    Norge
    MS-Off Ver
    Excel 2007
    Posts
    114

    Re: DropBox value triggering copypaste by "SEARCHING" for the correct cells to copypaste

    Hello,

    I tried Your code and it worked, but only With the FIRST item in the JV coloumn. All the other items don´t work.

    Here you find the Sample Workbook.

    Sample Workbook.xlsm


    Now you see, The first Choice to make is to choose a make/model in ComboBox1. Correct model line should be copypasted into the first available row. Just like the First items does With Your last code. THEN based on the Choice that is made in CB1, CB2 will change (this is already working) to only display the options that are available for that model. Now the same should apply here. For each item that is chosen in CB2 it should also find the corresponding row With info to copypaste into NeXT available line.

    Might I also note that after these two steps one should be able to just continue With a New model in CB1 and choose a New model ad option in CB2 and so on.....



    I can se that Your Logic in the code would work better if I rearranged all the data vertically rather than horisontally like now. This is no problem for me to do as long as I know that I have a working code that will do the job. Or is it possible to add several columns to the code (combobox1.Value, Columns("JY", "KK", "KW"), 0) ?



    AT last I want to comment Your question about where the MATCH data is located. The MATCH data is located in the Third coloumn in each data range, but for the Logic here to work I believe I will HAVE TO copy that info into a coloumn before the first coloumn of data, like JV in the example from prvious post. Otherwise I do not know if it will work. Looking at Your code again it just might. That means I would not have to copy the datawhich is already in the Third coloumn to the JV (the coloumn before).

    Please Login or Register  to view this content.

  4. #4
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: DropBox value triggering copypaste by "SEARCHING" for the correct cells to copypaste

    Hi tnuis, your sheet is a bit of a mess and I really don't have time right now to look at it.
    However, wouldn't this do the trick :

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    09-20-2012
    Location
    Norge
    MS-Off Ver
    Excel 2007
    Posts
    114

    Re: DropBox value triggering copypaste by "SEARCHING" for the correct cells to copypaste

    Quote Originally Posted by JasperD View Post
    Hi tnuis, your sheet is a bit of a mess and I really don't have time right now to look at it.
    However, wouldn't this do the trick :

    Please Login or Register  to view this content.

    Yes this Works now only I have to manually go into VBA into the code and hit RUN for it to work. Only the first cell/modell Works everytime without manually running the code. Seems there is only an event trigger missing here....
    Last edited by tnuis; 09-03-2013 at 09:35 AM.

  6. #6
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: DropBox value triggering copypaste by "SEARCHING" for the correct cells to copypaste

    This should work :
    did you remove the "Sub test()" before pasting it in...?

    Please Login or Register  to view this content.
    Click that damn * below if this helps

  7. #7
    Forum Contributor
    Join Date
    09-20-2012
    Location
    Norge
    MS-Off Ver
    Excel 2007
    Posts
    114

    Re: DropBox value triggering copypaste by "SEARCHING" for the correct cells to copypaste

    Quote Originally Posted by JasperD View Post
    This should work :
    did you remove the "Sub test()" before pasting it in...?

    Please Login or Register  to view this content.
    Click that damn * below if this helps
    Oh my god, offcourse. Wat dom van me

    Thank you so much. You saved me a loooooot of coding and I I learned something New today as well!

    Thanks thanks thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Sort Data based on Criteria and CopyPaste to another worksheet
    By HowieD in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-04-2013, 10:54 AM
  2. Replies: 20
    Last Post: 03-21-2013, 10:54 AM
  3. Error Generation during copypaste macro run
    By ravinder_tigh in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-26-2009, 01:00 AM
  4. CopyPaste
    By Ivica Lopar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-21-2005, 08:40 PM
  5. [SOLVED] FormatConditions appears to kill copypaste
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-18-2005, 06:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1