Copy unique values to another sheet within the same workbook

Adar123

Board Regular
Joined
Apr 1, 2018
Messages
83
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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

VBA Code:
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Try
Code:
Sheets("2").Range("K2:K164").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("1").Range("DE11"), Unique:=True
 
Upvote 0
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
 
Upvote 0
The code in Post #2 worked for me in excel 2007.
This works
Code:
Sheets("Sheet2").Range("A1:A26").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("Sheet3!C1"), Unique:=True
and this works also
Code:
Sheets("1").Range("A1:A26").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("2!C1"), Unique:=True
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,320
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top