VBA for searching for a value/highlight/copy

[mlibrett]

I'm looking to create a macro that looks down a specified column for 4 different values (something like A, B, C, D) and if those values are there for it to highlight and copy the rows.

Anyone have any idea how to make this?

[BigBas]

Do you have any more details? Copy the column to where?

A sample workbook maybe the best way to help here.

[mlibrett]

Sure, sorry about that!

I attached an example workbook. I am looking to look down the "Country" column for the value USA. If the value USA is there, I want to copy the entire row from that value and paste the whole row into a new sheet.

Sorry if I was unclear.

Thanks

[BigBas]

Try this code and let me know if it works. It should highlight all USA cells in light yellow, and copy the rows (unhighlighted) to sheet 2

Please Login or Register  to view this content.

[mlibrett]

That works very well, thanks very much. I am going to try to modify the code to use it with my larger data set. I'll let you know if I have any questions.

[mlibrett]

For some reason it copies and pasted the headers (row1) as well onto the new sheet. Do you know which line of your code is causing this ?

[BigBas]

It was written to include that. Try this modification and report back.

Please Login or Register  to view this content.

[mlibrett]

That works great, I'm just having trouble adjusting it to the data set I was trying to use it for. But that is simply because I am inexperienced with this stuff. Best way to learn though

Thanks again

[BigBas]

What troubles are you having with the data range? Perhaps we can help clarify things for you.

[mlibrett]

Hm it may be easier if there wasnt an autofilter being used. Do you think it's possible to do this using an if statement?

[BigBas]

Does this code work better for you?

Please Login or Register  to view this content.