VBA copy/ paste distinct values only, leave blanks and sort ascending

[mariusnielsen]

I'm fairly new to this and with some internet help I've managed to almost get it right, however would need my VBA to paste values only. I've tried with [FONT=&quot]Paste:=xlPasteValues without any luck. Anyone able to help me tweak this one a bit so it copies only values?

[/FONT]Sub copytoHScode()

Sheets("Manifest").Activate


Range("J10", Cells(Rows.Count, "J").End(xlUp)).Copy


Sheets("Summary by hs code").Activate


Range("A17").Activate


Sheets("Summary by hs code").PasteSpecial


Application.Selection.RemoveDuplicates Columns:=1, Header:=xlNo


Range("A17").Select
Range("A17", Range("A17").End(xlDown)).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo


End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub mariusnielson()
   With Sheets("Manifest")
      .Range("J10", .Cells(Rows.Count, "J").End(xlUp)).Copy
   End With
   With Sheets("Summary by hs code")
      .Range("A17").PasteSpecial xlPasteValues
      .Range("A17", .Range("A" & Rows.Count).End(xlUp)).RemoveDuplicates 1, xlNo
      .Range("A17", .Range("A" & Rows.Count).End(xlUp)).Sort Key1:=.Range("A17"), Order1:=xlAscending, Header:=xlNo
   End With
End Sub

 

[mariusnielsen]

Thanks Fluff.... Worked fine!

 

[Fluff]

You're welcome & thanks for the feedback