Custom Copy-Paste Code Help

[Panik]

On a Worksheet_Activate event I am looking for a way to copy all items found in a range $C$4:$I$100 (I'll name the range "Parts") to a different worksheet "Part Text", ignoring blanks and duplicates. I would like all items found in the range copied (ignoring blanks and duplicates that may exist in the copied text), but only paste to the "Part Text" worksheet if they do not already exist in the "Part Text" worksheet. I would like them pasted in Column "A" of the "Part Text" worksheet even though they are being copied from multiple columns and rows in the "Parts" range. I would like this to automatically happen when the "Part Text" worksheet is selected (Worksheet_Activate).

The idea being that when a new part is added to the "Parts" range. If it does not already exist in the "Part Text" worksheet, it will be added to the end of the list in Column "A".

Is this possible? I appreciate the help.

[Arnold Layne]

If I'm understanding your question correctly, I would approach this as a few separate steps...

First, I'd write a "ForEach" subroutine that takes one column at a time (so first range $C$4:$C$100, then range $D$4:$D$100, etc.) and adds each value found in the given range to the next unused row in column A on a third, hidden worksheet. (May or may not need to be done one column at a time, but that's the easiest way I can think of to make sure it captures all values from all columns in your range and puts them all in a single column.)

Second, once all the values are gathered into a single column, I would then write code to pull unique values only from the new single column and write those to a new column, either on the same sheet or an additional hidden sheet.

Once I had a single column containing only the unique values from the original range, I would then write another "ForEach" routine to go row by row and look for a match in your existing list on the "Part Text" sheet, and if no match is found, write the value that did not have a match to the next unused row in column A on sheet "Part Text".


These three actions should then all be able to be combined into a single subroutine triggered by a Worksheet_Activate event, once each task is working properly on its' own.






There's probably a simpler, cleaner way to do what you're wanting to do, but that's how I would approach it, being the novice that I am to VBA. I find that it's easier to figure out ways to do complex things by trial and error if I break the complex thing into smaller, less complex things and attack each individually. Hope that helps... If nobody offers a simpler solution, I can try to write some code for this using generic sample data over the next day or two. I'll keep an eye on this thread, and see what I can come up with as time allows.

[Panik]

I think I understand your approach. Thanks for the response. I need to digest it and see if I can make this work.

[Panik]

I Believe this is what you were talking about. I worked this out today, and it appears to be doing the job. I'm not sure if it is the best way to do it.

Please Login or Register  to view this content.