Charles Chickering
12/12/2006 7:09:00 PM
Try something like this:
Sub CopyUnique()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim cFrom As Range
Dim C As Range
Dim cSearch As Range
Set ws1 = Worksheets("H7469")
Set ws2 = Worksheets("H7469_STORICO")
Set cFrom = Range(ws1.Range("R1"), _
ws1.Range("R" & ws1.Rows.Count).End(xlUp))
For Each C In cFrom.Cells
Set cSearch = Nothing
Set cSearch = ws2.Range("R1:R" & ws2.Rows.Count) _
.Find(What:=C.Value, LookIn:=xlValues)
If cSearch Is Nothing Then
ws1.Range("A" & C.Row & ":R" & C.Row).Copy _
ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(1)
End If
Next
End Sub
--
Charles Chickering
"A good example is twice the value of good advice."
"sal21" wrote:
> In effect, in column R of sheet H7469 i have a unique index value.
> In column R of sheet STORICO_H7469 have a unique index value.
>
> Noew i want recopy all range of cells from H7469 in STORICO_H7469 only
> if in this sheet not is already present a unique index from column R of
> H7469....
>
> Example:
> admiting in H7469 column R & riga INDEX
> ="5236220-H7469-CON-4500-04/12/2006" and in column R of sheet
> STORICO_H7469 not is present this value copy all range of cells in
> sheet STORICO_H7469 , insted if this INDEX is present in in column R
> of sheet STORICO_H7469 skip the cells copy and return to find next
> INDEX... ecc....
>
>
> Sub FIND()
>
> Dim RIGA As String
> RIGA = 3
> Dim C As Range
>
> RIGA_S = Sheets("H7469_STORICO").Cells(65536, 1).End(xlUp).Row + 1
>
> While Not Sheets("H7469").Range("A" & RIGA) = ""
>
> INDEX = Sheets("H7469").Range("R" & RIGA)
>
> Set C =
> Sheets("H7469_STORICO").Columns("R:R").FIND((INDEX), LookIn:=xlValues)
>
> If Not C Is Nothing Then
>
> firstAddress = C.Address
>
> Do
> 'COPY IF NOT FOUND IN Sheets("H7469_STORICO")
> Sheets("H7469_STORICO").Range("A" & RIGA_S).Value =
> Sheets("H7469").Range("A" & RIGA).Value
> Sheets("H7469_STORICO").Range("B" & RIGA_S).Value =
> Sheets("H7469").Range("B" & RIGA).Value
> Sheets("H7469_STORICO").Range("C" & RIGA_S).Value =
> Sheets("H7469").Range("C" & RIGA).Value
> Sheets("H7469_STORICO").Range("D" & RIGA_S).Value =
> Sheets("H7469").Range("D" & RIGA).Value
> Sheets("H7469_STORICO").Range("E" & RIGA_S).Value =
> Sheets("H7469").Range("E" & RIGA).Value
> Sheets("H7469_STORICO").Range("F" & RIGA_S).Value =
> Sheets("H7469").Range("F" & RIGA).Value
> Sheets("H7469_STORICO").Range("G" & RIGA_S).Value =
> Sheets("H7469").Range("G" & RIGA).Value
> Sheets("H7469_STORICO").Range("H" & RIGA_S).Value =
> Sheets("H7469").Range("H" & RIGA).Value
> Sheets("H7469_STORICO").Range("I" & RIGA_S).Value =
> Sheets("H7469").Range("I" & RIGA).Value
> Sheets("H7469_STORICO").Range("J" & RIGA_S).Value =
> Sheets("H7469").Range("J" & RIGA).Value
> Sheets("H7469_STORICO").Range("K" & RIGA_S).Value =
> Sheets("H7469").Range("K" & RIGA).Value
> Sheets("H7469_STORICO").Range("L" & RIGA_S).Value =
> Sheets("H7469").Range("L" & RIGA).Value
> Sheets("H7469_STORICO").Range("M" & RIGA_S).Value =
> Sheets("H7469").Range("M" & RIGA).Value
> Sheets("H7469_STORICO").Range("N" & RIGA_S).Value =
> Sheets("H7469").Range("N" & RIGA).Value
> Sheets("H7469_STORICO").Range("O" & RIGA_S).Value =
> Sheets("H7469").Range("O" & RIGA).Value
> Sheets("H7469_STORICO").Range("P" & RIGA_S).Value =
> Sheets("H7469").Range("P" & RIGA).Value
> Sheets("H7469_STORICO").Range("Q" & RIGA_S).Value =
> Sheets("H7469").Range("Q" & RIGA).Value
> Sheets("H7469_STORICO").Range("R" & RIGA_S).Value =
> Sheets("H7469").Range("R" & RIGA).Value
> RIGA_S = RIGA_S + 1
> 'AND COPY IN Sheets("H7469_STORICO")
>
> Set C =
> Sheets("H7469_STORICO").Columns("R:R").FindNext(C)
>
> Loop While Not C Is Nothing And C.Address <>
> firstAddress
>
> End If
>
> RIGA = RIGA + 1
>
> Wend
>
> End Sub
>
>