excelvbaadvanced-filter

Excel 2016 Macro to Copy Range Excluding Duplicates

I have put together the following code to copy a range of IDs. The range contains many duplicates and I just want to paste one occurrence of each ID.

I keep getting a syntax error and I can't see what I am doing wrong. Can anyone point out the issue?

Thanks

Sub CopyIDs()

With ThisWorkbook.Sheets("DataTable").Range("A1", .Range("A1").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ThisWorkbook.Sheets("Analysis").Range("A8"), Unique:=True
    ThisWorkbook.Sheets("Analysis").Range("A8").Delete Shift:=xlShiftUp
End With

End Sub
Solution

  • You use "With" and "End With" in an incorrect way. If you want to skip to specify the "Date Table" sheets twice, you may refer below code

    With ThisWorkbook.Sheets("DataTable")
.Range("A1", .Range("A1").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ThisWorkbook.Sheets("Analysis").Range("A8"), Unique:=True

End With