CODEWITHSUNDEEP
excelvba
alex2002
alex2002

Reputation: 161

Unique Values range list

I am using the below code to extract unique values from a different sheet and paste them into another. The code works pretty fast which is great. However, I cannot figure it out how to paste the data only as values and not to copy the original format as well. I know that you need to set .PasteSpecial xlValues to the specific range, but when I tried to do that I get the error 1004.

Any suggestions would be must appreciated. Thanks!

Option Explicit

Sub Uniquevalues()
Dim lastrow As Long

Dim wk As Worksheet
Dim wk1 As Worksheet


Set wk = Sheets("X")
Set wk1 = Sheets("Y")


lastrow = Cells(Rows.Count, "B").End(xlUp).Row

    wk.Range("B3:B" & lastrow).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=wk1.Range("c4"), _  ' tried to set afer ("c4") .PasteSpecial xlValues, but it doesn't  work
    Unique:=True
    'wk1.Range("c4").PasteSpecial xlValues - tried like this as well and it doesn't work
End Sub

This is how I got rid of the copy format cells:

Sub format()

Dim wk2 As Worksheet
Dim lastrow1 As Long

Set wk2 = Sheets("Y")

lastrow1 = wk2.Cells(Rows.Count, "C").End(xlUp).Row
wk2.Range("c4:c" & lastrow1).Select
With Selection
Selection.NumberFormat = "General"
.Value = .Value
.ClearFormats

End With

End Sub

Upvotes: 1

Views: 67

Answers (1)

ashleedawg
ashleedawg

Reputation: 21647

Without your data I can't test this but try this:

Option Explicit

Sub Uniquevalues()

    Dim wk As Worksheet, wk1 As Worksheet, lastrow As Long
    Set wk = Sheets("X")
    Set wk1 = Sheets("Y")

    lastrow = wk.Cells(Rows.Count, "B").End(xlUp).Row
    wk.Range("B3:B" & lastrow).AdvancedFilter xlFilterCopy, , wk1.Range("c4"), True

End Sub

See: Range.AdvancedFilter Method (Excel)

Upvotes: 0

Related Questions