Copy rows based on criteria into new sheet, but not in the same sequential order

Hi Jerry and thank you so much for your assistance and instructions.

I'll modify the code and try it out in my workbook...let's hope it works.

One thing that I realized in the first sample workbook that I uploaded is that I didn't include some other fields that I want to have. Therefore, I've attached again with the desired modifications. Basically, on the new sheet there are two additional columns that I'd like to add. And, once the data is copied over to the new sheet, the respected cells in these columns would be typed in manually as needed.

I don't think this can be accomplished using the code you provided as these new columns will also need to update accordingly....but this data is not pulled from sheet 1.

Please take look at this modified workbook you don't mind, and your input, comments, recommendations are most appreciated!!

-kmsosaka

Hi Jerry, and thanks again.

Yes, your comments about data sheets vs. reports are well taken and understood. I like your suggestion about having the worksheet update automatically as data is changed. That makes things a lot easier.

I've put in the code into my workbook, and modifying now. The first thing I've noticed is that the data pulled from worksheet 1 is going into the correct columns on worksheet 2, but for some reason it is not compiling/sorting correctly. It appears that is duplicating the entries for each criteria and pasting into the new sheet. So, since I am sorting based on 3 different criteria, it is listing the same data 3 times.

Also, for whatever reason, it is also copying the row entries that do not meet these 3 criteria as well and placing it at the end of the list.

I'll be trying to go through the code right now and debug the areas that are causing this to occur....

-kmsosaka

Hi Jerry.

I was able to see where the error was coming from after searching the net to learn more about VBA coding and what each command that you wrote controls. It was from this line listing the ".Rows(4)" which didn't correspond to the same row on my actual sheet:

Please Login or Register  to view this content.

As you can see, I am a complete novice to macro programming, so troubleshooting take me time to figure very basic stuff out. But by trial and error, and a lot of searching on the net, I was able to basically get your macro to work (semi) properly!

Although I really liked your method that copies the manual entries (phone#/email) from sheet 2 back to sheet 1, in trying it out on my actual sheet I realized that other users who don't know about the added columns AA:AB will overlook this when they sort the data in the main table. Thus, though the data in the table will move around, if they don't include columns AA and BB when they sort, those entries will stay in same place and not correspond to the correct name anymore. So, the next time when sheet 2 is updated it will not match correctly.

Instead of creating columns AA and AB, I tried to designate these columns instead as Q:R and then hiding them. Thus, the data would within the "visible" range of the table (A:S) and would be included when users sort the data. However, for whatever reason it did not work, and I basically gave up on this idea. I've attached to worksheet with the modifications for your review.

Anyways, it's not absolutely necessary to have this "trick" included, as the main thing I wanted to accomplish is to be able to pull out and list the items that need follow-up. So, I think what you've provided thus far meets that! And, your patience and cooperation has been an interesting adventure for me as I continue to learn my way around the basics of VBA programming!

I'll keep twinkering around with your code, and I'll see if I can't get this just the way I'd like. Your suggestions keeps leading me to other ideas...and also makes me realize that I could've set up my original table better.

Thanks again Jerry. You are awesome!

Ahhh, I see what your saying. I'll give it a go and see if this will suffice.
-kmsosaka

Hi Jerry,

Sorry for not responding sooner....was on a short business trip and away from my computer terminal.

OK, so I've been able to modify and plug in your macro to get the desired results on my document on my computer. But, when my colleague saves the file and opens it up on his computer, the macro doesn't work properly...or not at all. I am wondering if it is something that I modified in the code that's causing the problem, or because I created the document on Excel 2003 and he is using 2007 version of Excel?

Anyway, I am including the code here to see if you can spot something wrong, as well as attaching a simplified version of what my actual workbook looks like.

Please Login or Register  to view this content.

The modification I made is because the document info. is being pulled from is protected, so needed to have the macro unprotect, execute the code, and the re-protect the document. For whatever reason, on some of the files, when it re-protects the document it also changes the "Protect Sheet..." settings as well. This results in the "locked" cells to become unlocked even with the document protected.

Any ideas on what's going wrong here?

-kmsosaka

Fantastic. Will take a look into the *.xlsm file issue.

OK, so there just seems to be still one problem occurring. When the "Candidate Activity" sheet gets re-protected by the macro, it seems to be resetting the "Protect Sheet..." selections. What I want (and need) is the ability to (1) Select Unlock Cells; (2) Sort.

After the macro runs it does indeed protect the document, but it also removes the "Sort" function. Without the ability to sort, this table will not be easy to navigate.

Sorry again Jerry, but any help appreciated.

-kmsosaka

Think I got it solved:

Please Login or Register  to view this content.

Is this the proper method?

LOL
Yeah, it works....and that's a great thing! Jerry....thank you thank you thank you! You taught me a lot over the past couple of weeks, and you must be one patient and nice guy to put up with an excel illiterate like myself!

But, through our back and forth communications, I'm realizing that Excel can really do some cool things.....that is, if you know what you're doing. Haha. Hope to get to that level some day.

Cheers!

ps: I'm sure you'll see me on this site later on with other "problems" to be solved.