Search Column A and copy to another Column

[trevor2524]

Hi, I'm trying to come up with a macro that will search Column A for specific fields. If the field equals the word Red then it will copy the cells from that column to column D. It will then place it in Column F starting at cell 20. It will then go back to Column A and check the next row. If it equals Blue it will then copy the first four cells in that row and then paste them in Column K cell 20. If the cell equals Red it will then go back to Column F and go to the next available cell in column F after row 20. The total options I would need would be Red, Blue, Yellow and its the words not the colors that I'm looking for. Any help is greatly appreciated.

[jason_lee_91]

Try this.

Please Login or Register  to view this content.

[trevor2524]

It works, the only thing i need help figuring out is I'm trying to get it to start pasting on row 20 in all the columns and if it comes across another record that corresponds to that color it will find the next empty row in the column its suppose to go in. Right now it sort of does that except it starts the pasting in row 2. Thanks again for your help.

[trevor2524]

Also there will be a fourth option I will have to add which is if the cell is blank if its blank it will copy those rows into Column O. So I will need to put the limit on how many rows it checks at 5000.

[jason_lee_91]

You can make the minimum row 20 but putting a check in.
And as you can see by the case statements, you can add more options

Please Login or Register  to view this content.

[trevor2524]

Works Perfect. The only thing i wonder is how do i stop it from searching Column A once it reaches 5000 rows?

[jason_lee_91]

Kind of like how you limit nbot.
Also, This is my fault, but you should define all your variables.

Please Login or Register  to view this content.

[trevor2524]

Thank You!

[jason_lee_91]

No problem.
Glad it worked for you.