Import file and scan for duplicates in column

[MrSmith484]

Dear Excel superusers, I´m an Excel newbie looking for help with this piece code

I currently have this piece of code, that aggregates two Excel sheets by clicking on a button in my sheet and prevent using the same file twice:

Option Explicit

'Aggregate different sheets
Sub GetRisks()

Dim vFile As Variant
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet
Dim lRowCopyFrom As Long 'Lst column in sheet
Dim nm As Name 'Deletes defining names
Dim r As Variant


Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = ActiveSheet
'-------------------------------------------------------------
'Open the files which is copied

vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
"*.xl*", 1, "Select Excel File", "Open", False)

'If Cancel then Exit
If TypeName(vFile) = "Boolean" Then
Exit Sub
Else
Set wbCopyFrom = Workbooks.Open(vFile)
Set wsCopyFrom = wbCopyFrom.Worksheets("Sheet1")
End If

'------

'Check if the file is already added and close it if that is the case
Set r = Sheets("Sheet2").Columns("B").Find(What:=vFile, LookIn:=xlValues)
If Not r Is Nothing Then
MsgBox "Error: The chosen file is already in the sheet." & vbNewLine & "Cannot be inserted twice"
wbCopyFrom.Close SaveChanges:=False
Exit Sub


End If




'--------------------------------------------------------------
'Copy all rows in Sheet1 except from header header

'Find last row
lRowCopyFrom = wsCopyFrom.Range("A" & Rows.Count).End(xlUp).Row

'Copy data into aggregated sheet in first available row
wsCopyFrom.Range("A9:S" & lRowCopyFrom).Copy Destination:=wsCopyTo.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

'Close opened file
wbCopyFrom.Close SaveChanges:=False


'Add file name in sheet 2
Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = vFile

'Remove defining names
For Each nm In wbCopyTo.Names
On Error Resume Next
nm.Delete
Next nm

Call CountColoredCells

End Sub

What I would like to add to the code, is to check for duplicates in column "A" That is, if the aggregated sheet already has e.g. "Peter" in a cell column "A" and the file which I want to add to the current file has "Peter" in column "A" as well, then a Msg box with a warning appears. Excactly like this piece of code, that I cannot seem to fit into my current code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim son As Long, onay, bul As String
Dim ara As Range

If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

son = Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
If WorksheetFunction.CountIf(Range("A2:A" & son), Target) < 1 Then
Exit Sub
End If
If WorksheetFunction.CountIf(Range("A2:A" & son), Target) > 1 Then

bul = Empty
Set ara = Range("A2:A" & son).Find(Target, , xlValues, xlWhole)
If Not ara Is Nothing Then
adres = ara.Address
Do
bul = bul & ara.Row & " - " & Cells(ara.Row, "A") & Chr(10)
Set ara = Range("A2:A" & son).FindNext(ara)
Loop While Not ara Is Nothing And ara.Address <> adres
End If
onay = MsgBox("Row : Records :" & vbCrLf & Chr(10) & bul & vbLf & "Do you want to enter?", vbYesNo)
If onay = vbYes Then MsgBox "Recording has been completed.", vbInformation, "Info"
If onay = vbNo Then Target.ClearContents

End If
End Sub

That is, the code Works, but not together with my current code - I don´t want a "Private sub"but just a "sub" .

Thanks in advance