Here you go!
Option Explicit
Option Compare Text
Sub CompareValues()
Dim wsCopies As Worksheet
Dim rFirst As Range
Dim rSecond As Range
Dim rCell As Range
Dim rMatch As Range
Dim lCount As Long
Dim bScrUpd As Boolean
Dim bEvents As Boolean
Dim lCalc As Long
'Improve performance
With Application
bScrUpd = .ScreenUpdating
bEvents = .EnableEvents
lCalc = .Calculation
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
'Change A1:A19 to your range with duplicate values
Set rFirst = Sheets("src").Range("A1:A19")
'Change A1:A6 to your range with no duplicate values
Set rSecond = Sheets("Mast").Range("A1:A6")
'Set worksheet Copies = worksheet object wsCopies
On Error Resume Next
Set wsCopies = Sheets("Copies")
wsCopies.Cells.Clear
If Err.Number <> 0 Then
Set wsCopies = Worksheets.Add(After:=Sheets(Sheets.Count))
wsCopies.Name = "Copies"
End If
On Error GoTo 0
'Find rSecond in rFirst and add to Copies worksheet
For Each rCell In rSecond
Set rMatch = rFirst.Find(rSecond, LookIn:=xlValues, LookAt:=xlWhole)
If Not rMatch Is Nothing Then
Set rCell = rCell.Resize(1, 2)
wsCopies.Cells(lCount + 1, 1).Resize(1, _
rCell.Columns.Count).Value = rCell.Value
lCount = lCount + 1
End If
Next
'Clean up
Set rFirst = Nothing
Set rSecond = Nothing
Set rMatch = Nothing
Set rCell = Nothing
'Restore Application settings
With Application
.ScreenUpdating = bScrUpd
.EnableEvents = bEvents
.Calculation = lCalc
End With
End Sub
Good luck!
Bookmarks