I have two excel files.

One file has word William in Col Z
A B C D E F G H I J
skipped.. Z
123 34A B34 C54 D34 323 2 3 5
6 William
122 34b b34 r54 f34 321 3 a4 e6 7
121 34c c34 g54 g34 j322 m2 b3 f5 6
William
120 34d e34 h54 h34 k321 n3 c4 f6 7
119 34e e34 i54 i34 l322 o2 d3 h5
6 William
File continues....... till 500 rows

2nd file has word William in all values of Col Z
A B C D E F G H skipped.. Z
123 Mike Lee C54 D34 323 2 3 5 6 William
222 James Lee b34 r54 f34 321 3 4 6 7
William
321 Shawn Lee g54 t34 322 2 3 5 6 William
421 Jason Lee e54 f34 1322 3 4 6 9
William
File continues....... till 500 rows


I want to replace (B to J col) values of file 1 with (B to J col) of
file 2 if COL Z matches William.
It just copies the next row (COL b to COLJ VALUES with william in col
Z) of from file 2.


Final File
A B C D E F G H skipped.. Z
123 Mike Lee C54 D34 323 2 3 5 6 William
122 34b b34 r54 f34 321 3 a4 e6 7
121 James Lee b34 r54 f34 321 3 4 6 7
William
120 34d e34 h54 h34 k321 n3 c4 f6 7
119 Shawn Lee g54 t34 322 2 3 5 6 William
File continues....... till 500 rows

Any macros will be appreciated

thanks