Macro to find and copy/paste invalid data

Hi,

I am looking for a macro that can scan columns B, C, D and E for any data that is circled as invalid, and then paste the corresponding values from column A into a new range.

For example (invalid data is bold and red. In reality these cells are circled):

A B C D
2 a b d
6 a b d
4 a b d
3 a b d
5 a b d
7 a b d


New range:
6
3
7

I hope this makes sense!

Thanks,
Jamie

What does "circled" to you mean? It might not mean the same thing to me. Be more specific on what distinguishes the cells (If "circled" means bold and red only, is it done by conditional formatting?).

into a new range

Any more information on what this new range is? Is it for example column J? How about Sheet4 column C?

Apologies for not making clear. By 'circled', I'm referring to cells that have been circled by the 'Circle Invalid Data' function in Data Validation.

The new range can be anywhere. In the example above, maybe starting in F1.

Hope this has cleared things up

I had to look this one up. Learned something new today. Credit of this goes to cytop on ozgrid, I just modified his code to match your needs. It worked when tested on a test workbook:

Please Login or Register  to view this content.

Hi stnkynts, thanks for your help! This works great for listing the invalid data, but doesn't seem to list the corresponding value in column A (unless I'm doing something wrong?)

Thanks,
Jamie

Woops that is my fault. Try this:

Please Login or Register  to view this content.

Amazing! Thanks