For Next Loop with an if statment (or replacing an if statment ?)

Hello.
Open to suggestions on how to best accomplish the following. Thanks in advance to anyone who can help:

I'm working on an excel macro that compares the value in a cell against the value in another series of cells in another column.
If there is a match, then there is a copy paste done of cells in that same row and it's pasted to a worksheetname that matches the value of another cell in that row.
I have the part about worksheet name (thanks to a helpful member in this forum). What I am now looking for is a way to run through the comparisons with a for next instead of a very long set of if/then statements
Below are the first 3 if then statements (there's over 100 rows/comparisons that need to be done).

Please Login or Register  to view this content.

......
Also, after running through that if/else statement above 100 times (comparing a2 to ad2 thru ad 101), it then needs to run similar if/else satements
for row 3 100 times (coparing a3 to ad2 to ad101), like below.
So not sure if there's a way to accomplish both of these comparisons in one for next loop or if it needs to be 2 separate for next loops.

Please Login or Register  to view this content.

----

Thanks to anyone who has read through all this and extra thanks to anyone who can help.

Try something like this:

Please Login or Register  to view this content.

Can you post a workbook (data can be fake but should be representative, and the structure should match your actual workbook). It's going to be simplest to use a filter I suspect, assuming that the sheet name values in AC are the same for any given value in column B?

Hi RoryA-
As requested/suggested, attached is a sample file with dummy data.
I put a line of data already in place for a couple tabs so the part about finding first row with data and going one below that to start can be tested.
As you can see the values in AC are not always the same as in B. For example, Column B has values of Blue1, Blue2, and Blue3 that go to the sheetname of Blue which is in AC. In some cases the values do match (like values of Belgium in B and AC) but not all.
Hope you and family are enjoying the holidays and thanks so much for your help. (same goes to anyone else who wants to chime in on this thread!)

-Mike Gee

Just a small change then:

Please Login or Register  to view this content.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.