Copy and paste data if condition is met

[fabian_76]

Hi I would like some help with a VBA code. I am struggling to collect all this information from a spreadsheet. Notes: the spreadsheet may have 10, 20, 100 records horizontally however all records are below the word REJECT. It also may have from 1 up to 10 records vertically. Basically I want to copy the four cells below the word REJECT (in yellow) plus the 3 adjacent cells (please see attached example) and paste them in the sheet "Data".
The condition would be "if there is data below the word REJECT, select the data below, copy and paste them in the sheet Data". If possible put a message that says the number of records registered.

Thanks you in advance, this will save me a lot of time.

[Bernie Deitrick]

Use this code, and run "FindValues" - note that your sheet Production had an extra space at the end, which you may want to fix, both on the sheet and in the code:

Please Login or Register  to view this content.

[fabian_76]

Thank you Bernie for the quick reply. I gave it a try and it worked, however when I added additional information the code did not consider it, please refer to the attach and see records in green. Those were not imported.
What I noticed is that the code is only copying what is just below the word REJECT but there could be more rows with information from 1 up to 10 records / rows.

Thanks again

[Bernie Deitrick]

I'm not sure what I did but I had a selection object in this line that should not have been there - the second line of "DoCell" should have been

Please Login or Register  to view this content.

As far as the 1 to 10 values, is there a consistent, logical way to determine the size of the data set that should be looked at?

[Trebor76]

Hi fabian_76,

Try this:

Please Login or Register  to view this content.

Regards,

Robert

[fabian_76]

Thank you Bernie you helped me a lot.