Omend code to include 2 rows above found row.

[abjac]

Hi I have below code. It search in sheet 2 column E to L after the input word. When found it copy all rows (Column E to L, Sheet2), with the found word to Sheet 3. Column E to L.

I need this to include the first 2 above rows with colorindex 38.
So example if it find name Peter in sheet 2 row E10:L10. then it have to include the 2 rows above with color index 38.

And the same for the next found Peter
Have a look at the test sheet.
Thanks in advance
Abjac

My code.

Please Login or Register  to view this content.

[nilem]

Hi Abjac,
try it

Please Login or Register  to view this content.

[abjac]

Hi Nilem. Its a great code, I just made a little mistake. Can your code be changed so it only copy the rows with the name Peter. Where there is text in one of the cells from F to L. There is a little change in the testsheet below where you can see what i want.Please have a look.
Sincerely
Abjac

[nilem]

try

Please Login or Register  to view this content.

[abjac]

HI Nilem. Its working perfect really great wow. It only have a little problem it also copy from cell B and C. If you try to put some value in to example B3 , B5; C3 ; C5 it will copy them also.
And also a little request. Is it possible to make a blank row after Peter to the next 2 colored and Peter, so its seperated?
Sincerely
Abjac

[nilem]

Okay, try

Please Login or Register  to view this content.

[abjac]

HI Nilem and thanks allot. It really works just perfect. It have a little thing with the inserted row. It copy the colorindex also there. But i think i can fix that. Thanks allot for you help.

Have a nice day

Sincerely
Abjac