Search for keywords and copy rows containing keywords to new sheet

Another one

Ollie and AB beat me to it, but here's another one:

thanks to you guys.

AB33: your macro works very well with the example but it fails when I copy it to my real excel file with 3500 rows. The data actually starts from row 5 in my real excel sheet. The debug highligths the line If x(i,3) like ... and says x(i,3)=<subscript out of range>.
If I change to i=5 instead of i=2 as the list starts from row 5 then I get another bug at the line .Range("A2").Resize(n, UBound(y,2))=y

OllieB: In the example sheet I have 2 lines containing the word "Structural" but only one of them is copied.

It does not matter which row you start since we are testing for each row for a condition.
I know why the code fails.
My assumption was you have data starting from row 1

Change this line from a1 to a5

Please Login or Register  to view this content.

If you have gaps in your data, you can use this line, instead.

Please Login or Register  to view this content.

Thanks djdjdj. it works for both the example and the bigger file. I had to change For Each cell In Range("c2:c" & lastrow) to For Each cell In Range("c5:c" & lastrow)

I forgot that but how would do if one would like to have the corresponding index nr from sheet 1 (nr in the grey column in the left of the sheet) copied together with the lines in sheet2 and sheet3. this would basically add a column in sheet 2 and sheet3. This would be nice for me to be able to go back on sheet1 and check.

thanks a lot

Hi again,

Same thing but with added column for "Index Number"

thanks guys. it works fine now.