Take a look at below code:
Option Explicit
Sub CopyDataSetUnique()
Dim srcWsh As Worksheet, dstWsh As Worksheet
Dim i As Integer, j As Integer
Dim srcRange As Range
On Error GoTo Err_CopyDataSetUnique
Set dstWsh = ThisWorkbook.Worksheets(1)
j = 2
For i = 2 To ThisWorkbook.Worksheets.Count
Set srcWsh = ThisWorkbook.Worksheets(i)
Set srcRange = srcWsh.UsedRange.Columns(1)
If Not srcRange.Address Like "$A$*" Then
MsgBox "There's no data in column 'A' in sheet: '" & srcWsh.Name & "'", vbInformation, "Information"
GoTo SkipNext
End If
srcRange.Copy
dstWsh.Range("A" & j).PasteSpecial xlPasteValues
j = j + srcRange.Rows.Count
SkipNext:
Next
dstWsh.UsedRange.RemoveDuplicates Columns:=1, Header:=xlNo
Exit_CopyDataSetUnique:
On Error Resume Next
Set srcRange = Nothing
Set srcWsh = Nothing
Set dstWsh = Nothing
Exit Sub
Err_CopyDataSetUnique:
MsgBox Err.Description, vbExclamation, Err.Number
Resume Exit_CopyDataSetUnique
End Sub
Above code copies data from column A in worksheets {second to last one} into column A in worksheet{first}
After that,
RemoveDuplicates
method is called to get unique data.