The code below compares two worksheets which both have 10 columns,
if there are any differences the entire row is copied and pasted to a separate
Worksheet "Changes".
My problem is that if in any row there is e.g 3 changes, the same row is
copied over three times, obviously I want the row to be copied over only once,
how do I adjust this code to do this.
Help is greatly appreciated


k = 6
For i = 1 To 20
For j = 1 To 10
If Worksheets("Jobs").Cells(i, j) <
Worksheets("Jobs").Cells(i, j) Then
Worksheets("Jobs").Cells(i, j).EntireRow.Copy
Destination:=Worksheets("Changes").Cells(k, 1)
k = k + 1
End If
Next j
Next i

One way:

k = 6
For i = 1 To 20
For j = 1 To 10
If Worksheets("Jobs1").Cells(i, j) < _
Worksheets("Jobs2").Cells(i, j) Then
Worksheets("Jobs1").Cells(i, j).EntireRow.Copy _
Destination:=Worksheets("Changes").Cells(k, 1)
k = k + 1
Exit For
End If
Next j
Next i

In article ,
teresa wrote:

The code below compares two worksheets which both have 10 columns,
if there are any differences the entire row is copied and pasted to a separate
Worksheet "Changes".
My problem is that if in any row there is e.g 3 changes, the same row is
copied over three times, obviously I want the row to be copied over only once,
how do I adjust this code to do this.
Help is greatly appreciated


k = 6
For i = 1 To 20
For j = 1 To 10
If Worksheets("Jobs").Cells(i, j) <
Worksheets("Jobs").Cells(i, j) Then
Worksheets("Jobs").Cells(i, j).EntireRow.Copy
Destination:=Worksheets("Changes").Cells(k, 1)
k = k + 1
End If
Next j
Next i

First it looks like you are comparing the same cell to itself
{Worksheets("Jobs").Cells(i, j)}. Probably a typo. Check the destination
sheet to see if it already has that value in it...
k = 6
For i = 1 To 20
For j = 1 To 10
If Worksheets("Jobs").Cells(i, j) < _
Worksheets("Jobs").Cells(i, j) And Worksheets("Jobs").Cells(i, j) < _
Worksheets("Changes").Cells(k, 1) Then
Worksheets("Jobs").Cells(i, j).EntireRow.Copy _
Destination:=Worksheets("Changes").Cells(k, 1)
k = k + 1
End If
Next j
Next i


Mike F

"teresa" wrote in message
...
The code below compares two worksheets which both have 10 columns,
if there are any differences the entire row is copied and pasted to a
separate
Worksheet "Changes".
My problem is that if in any row there is e.g 3 changes, the same row is
copied over three times, obviously I want the row to be copied over only
once,
how do I adjust this code to do this.
Help is greatly appreciated


k = 6
For i = 1 To 20
For j = 1 To 10
If Worksheets("Jobs").Cells(i, j) <
Worksheets("Jobs").Cells(i, j) Then
Worksheets("Jobs").Cells(i, j).EntireRow.Copy
Destination:=Worksheets("Changes").Cells(k, 1)
k = k + 1
End If
Next j
Next i