Copy unique values to another sheet within the same workbook

[Adar123]

Hi there,

as the title suggests, I am attempting to copy unique values to another sheet within the same workbook.

The dataset contains multiple columns of data. I am attempting to copy-paste unique values from Sheet("2") column K to Sheet("1") cell DE11. (The sheets actually carry such names).

A few attempts failed with my latest code throwing out run-time error '1004' Method 'Range' of object '_Global' failed when I reach code with calculation lastRow

Sub UpdateList()

' UpdateList Macro
'    Dim myApp As Excel.Application
    Dim wkBk As Workbook
    Dim wkSht As Object
    Dim lastRow As Long
    
    Sheets("2").Select
    Range("K1").Select
    Range("K2:K164").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "2!U2"), Unique:=True
        
    lastRow = Range("U2" & Rows.count).End(xlUp).Row

    wkBk.2.Range("U2:U" & lastRow).Copy
    Set wkSht = wkBk.Sheets("1")
    wkSht.Activate
    Range("DE11").Select
    wkSht.Paste
    
End Sub

 

[jolivanes]

Try

Sheets("2").Range("K2:K164").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("1").Range("DE11"), Unique:=True

 

[Adar123]

I get error that the extract has an illegal field name, it seems that advance filter does not allow to extract to another sheet: I tested it manually, hence need to copy somewhere on the same sheet first and then copy over extracted data to the sheet I like

 

[jolivanes]

The code in Post #2 worked for me in excel 2007.
This works

Sheets("Sheet2").Range("A1:A26").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("Sheet3!C1"), Unique:=True

and this works also

Sheets("1").Range("A1:A26").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("2!C1"), Unique:=True

 

[jolivanes]

Also, the Copy to Column has to have the same header as the Copy from Column.
If A1 and C1 in the code snippet of Post #4 are different you'll get the message you mentioned.

 

[jolivanes]

If you change the C1 to C2, it'll work or if you change the A1 to A2 and the C1 to C2 it'll also works.