Copy and paste: Cycle thru identical worksheets, remove duplicate values

Hi all,
I have taken my workbook and worksheets filter, copy, and paste macro as far as I can.

The idea is to have a macro button on the "Summary" tab of my attached (and simplified) Excel file.

The assigned macro will cycle through the subsequent worksheets to filter for the information I need, copy the filtered information, and then paste filtered information under its respective column on the "Summary" worksheet while also removing duplicate values (so that all values only appear once in each column on the "Summary" worksheet).

The macro will then move to the next worksheet to complete the same task until the end of the worksheets.

My VBA code for filtering, copying, and cycling work perfectly and as intended. But I am failing to 1) successfully paste the copied information on the "Summary" worksheet in the correct column before the macro moves to the next worksheet, and 2) as a result have also been unsuccessful in removing duplicate values after pasting.

I've included some conditional formatting in my excel file to show the values that have duplicates, as well as a desired results worksheet.

My VBA code is below. I don't know if what I am hoping to accomplish is possible, but right now things fall apart at the "Paste.RemoveDuplicates" line.

Any help you can offer would be much appreciated.
Thanks in advance.


Sub CopyPasteRemoveDuplicates()

' Copy and paste but remove duplicates on destination sheet Macro

Application.ScreenUpdating = False

Dim r As Integer
r = 1
Dim sourceSheet As Worksheet
Set sourceSheet = ActiveSheet

Do

On Error GoTo ErrorHandler

Worksheets(ActiveSheet.Index + 1).Select

On Error Resume Next

Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1").AutoFilter Field:=1, Criteria1:="123456789", Operator:=xlFilterValues
ActiveSheet.Range("$B$1").AutoFilter Field:=2, Criteria1:="<>0", Operator:=xlFilterValues

Application.CutCopyMode = False

ActiveSheet.Range("$B$2").Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Summary").Cells(2, r).Paste.RemoveDuplicates Columns:=r, Header:=xlYes

Loop

ErrorHandler:
Call sourceSheet.Activate

Application.CutCopyMode = False

End Sub

Thank you jindon for helping me brainstorm.
I used your "Cells" code and included into mine. Now it works perfectly!
To summarize the code: the macro goes to each worksheet, converts the data I want using Columns to Text so that the filters recognize the values as numbers, filters the columns by my criteria, then it copies and pastes the data I need into my main summary worksheet in sequence while removing duplicate values.
Marking this thread as "solved"!

Sub CopyPasteRemoveDuplicatesMerge()

' Copy and paste but remove duplicates on destination sheet Macro

Application.ScreenUpdating = False

Dim sourceSheet As Worksheet
Set sourceSheet = ActiveSheet

Dim i As Long

Do

i = i + 1

On Error GoTo ErrorHandler

Worksheets(ActiveSheet.Index + 1).Select

ActiveSheet.Range("$B$2").Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("$B$2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

On Error Resume Next

ActiveSheet.Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1").AutoFilter Field:=1, Criteria1:="123456789", Operator:=xlFilterValues
ActiveSheet.Range("$B$1").AutoFilter Field:=2, Criteria1:="<>0", Operator:=xlFilterValues

ActiveSheet.Range("$B$2").Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select

Selection.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=sourceSheet.Cells(2, i), Unique:=True

Loop

ErrorHandler:
Call sourceSheet.Activate

Application.CutCopyMode = False

End Sub