Sub ReplaceColumnns()

Set BK1Sht = Workbooks("book1.xls").Sheets("sheet1")
Set BK2Sht = Workbooks("book2.xls").Sheets("sheet1")

BK2RowCount = 1
For BK1RowCount = 1 To 500
With BK1Sht
If .Range("Z" & BK1RowCount) = "William" Then
BK2Sht.Range("B" & BK2RowCount & ":J" & BK2RowCount).Copy _
destination:=.Range ("B" & BK1RowCount)
BK2Sht.Rows(BK2RowCount).Delete
End If
End With
Next BK1RowCount
End Sub

" wrote:

Excellent.

How can i delete the rows from file b wich are copied.
thanks

On Sep 2, 12:33 pm, Joel wrote:
Sub ReplaceColumnns()

Set BK1Sht = Workbooks("book1.xls").Sheets("sheet1")
Set BK2Sht = Workbooks("book2.xls").Sheets("sheet1")

BK2RowCount = 1
For BK1RowCount = 1 To 500
With BK1Sht
If .Range("Z" & BK1RowCount) = "William" Then
BK2Sht.Range("B" & BK2RowCount & ":J" & BK2RowCount).Copy _
destination:=.Range ("B" & BK1RowCount)
BK2RowCount = BK2RowCount + 1
End If
End With
Next BK1RowCount
End Sub



" wrote:
Thanks alot.

I really appreciate your help. It is almost done.

It is working excellent except one problem.
File A contains "William" in some values of Col AF.
But File B contains "William" in all values of Col AF

It is copying data from exact row number of file b from Col AF. But i
want to copy data from first ROW 1 (file b), THEN 2(file b) , THEN
3(file b), AND SO ON(file b becuase it contains "william" in all
line)).

For example, if FILE A is
A..B....C.....AF
a..1...2......William
b..3...4......
c..5...6......
d..7...8......William
e..9...10......William

File b
A..B....C.....AF
q..11...12......William
w..13...14......William
e..15...16......William
r..17...18......William
s..19...20......William

Result is
a..11...12......William
b..3...4......
c..5...6......
d..13...14......William
e..15...16......William

On Aug 30, 4:30 pm, Joel wrote:
This code may work. I think I undersant what you want but am not 100% sure.
Change the first two lines as required to make the workbook names and sheet
agree with your spreadsheet.

Sub ReplaceColumnns()

Set BK1Sht = Workbooks("book1.xls").Sheets("sheet1")
Set BK2Sht = Workbooks("book2.xls").Sheets("sheet1")

For RowCount = 1 To 500
With BK1Sht
If .Range("Z" & RowCount) = "William" Then
BK2Sht.Range("B" & RowCount & ":J" & RowCount).Copy _
destination:=.Range ("B" & RowCount)
End If
End With
Next RowCount
End Sub

" wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -