Yet another copy data from one sheet to another sheet problem

  • I read all the threads related to this topic and learned a lot but I'm still having a problem with my code. It's looping infinitely (not sure I did the loop right - well obv not if it doesn't stop looping, oy) and I can't seem to make it copy the data I want it to copy from the data dump worksheet for searching on the master. I am hoping someone can help.


    I have a worksheet with a data dump (test). Column J contains a part number. I need to match this part number to the master worksheet (test-data), copy that row's data from columns AN through BO, and paste the copied data back to the data dump worksheet. Then I need it to move down one row, and repeat, until it encounters a blank field. Totally new to VBA and coding in general (this is my first try). What am I doing wrong?


  • I should also add that the dims I set for srcWks and destWks are intended for inclusion in the code later once I iron out the functionality where I coded specific worksheet names

  • It would be easier to test possible solutions if you could attach copies of the two files. Explain in detail referring to specific cells, rows, columns and sheets exactly what you want to do using a few examples from 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.

  • thanks for the reply. All the data in these sheets is confidential so I had to set up some sample data. I kept the structure of the data the same but all real data has been redacted. Here's the task - well, the part of it I'm working on right now, anyway.


    "Sample dump" is a monthly data dump of changes I get. Blank columns contain data but are irrelevant for this part of the task so I left it out.
    "Sample master" is a sample snippet of my master sheet. (the master is 320ish MB!!) Blank columns contain data but are irrelevant for this part of the task so I left it out.
    For this part of my task, I have to locate the part number in column J (not limited to numbers btw, this can contain letters and sometimes even brackets or parentheses so all searches must be performed as a character string) on the "sample dump" (column J), and match this part number to the master in the same column J.
    THEN, I need to follow that row across to columns AN-BO and copy this range of data back to the dump file in the same corresponding columns (AN-BO) for that original part number I searched up.


    One thing I just noticed and did not account for in my code above is that there are now sometimes blanks in each part column. So ending on an "if blank = true" will no longer work for me. I'll have to set the loop to process a range of rows for column J - say 2-900 - and end at the end of the range.


    Not relevant to this task, but may help with context:
    I'll have to repeat this task for each column of part numbers (J through Z) and corresponding ranges similar (but different) to the AN-BO range I outlined above. After this half of the task is complete, then I will need to delete rows in the master based on column I (name) and paste (append) all data added/updated to the dump file to the end of the master file. I hope this makes sense.
    This is a recurring monthly task that takes me over a week to do manually and I'd like to better utilize my time.


    Thanks in advance!

  • My apologies for the delay. Ever since the user interface of the forum was changed last year, it's given me nothing but problems. It's very slow, it hangs, it freezes up and a few other problems.
    I noticed that you have duplicate values in column J of both files. How do you want to handle this situation?

    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 encounter with a value in column J = the row I want to copy from.


    Any row with a matching J value will work, they're all the same, but to make things easier, first instance of that value In J is fine.


    Thank you for any help you can provide.

  • Place this macro in a standard module in the Master workbook. Change the workbook name (in red) to suit your needs. Make sure both workbooks are open and run 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.

  • Hi Mumps,
    Thank you for your help. When I try the macro (I am using the same test files as I uploaded here) I get "Subscript out of range". Thoughts?


    Edit. I'm a dummy. This works. I put it into the wrong spreadsheet. /facepalm. One final issue - if there are blanks in column J, it stops. You can reproduce if you remove one of the values "4012" in column J. Removing any of those and then running it will show you what I mean.

  • The macro worked properly when I tested it on the 2 files. Which line in the code is highlighted when you click "Debug"? Are both workbooks open?

    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.

  • Code works beautifully.... I just ran this on real data - I think I see why I thought it would stop on a blank line. It actually doesn't. This has revealed how dirty the "master" file actually is. All that aside, for the data in the "dump" file, if there are multiple identical values in column J it skips the rest of that particular row and goes down to the next unique value in the data dump column J. How to prevent it skipping to the next unique value? Sorry my previous reply was not clear.

  • The problem is that there are 66 occurrences of "4012" in the Master and 2 occurrences of "4012" in the dump. How do you decide which row in the master corresponds to which row in the dump?

    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.

  • Any row in the master is fine. I made the following edit and it works exactly how I need it to:

    Code
    1. desWS.Range("J1:J" & bottomJ).AdvancedFilter Action:=xlFilterInPlace, Unique:=[B]False[/B]


    I would love to buy you a coffee or a drink of your choice! You have saved me so much time and headaches. Thank you sir.

  • You are most welcome. :) This revised version should also work.

    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.

  • That's even better! This one solved a problem you didn't even know I had. LOL I just found in the original macro where some of my row data was being scrambled. I have 226 total columns in the master - heh. This seems to prevent that from happening. Again, many thanks!

  • [USER="138669"]Mumps[/USER] :-


    I like what you've done above for LadyKiwi. Very nice! I clicked on "like" in your post #13 but now its showing as "unlike"! How does that work?


    Cheerio,
    vcoolio.

  • [USER="280827"]vcoolio[/USER]
    Thank you for the rep and kind words. :) I think it's showing as a "Like" now.


    [USER="332562"]ladykiwi24[/USER]
    You are very welcome. :) You can also delete this line of code since the macro doesn't use filtering now:

    [SIZE=12px]

    Code
    1. desWS.Range("J1").AutoFilter

    [/SIZE]

    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.