Results 1 to 4 of 4

Thread: VBA copy/ paste distinct values only, leave blanks and sort ascending
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2011
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 Paste:=xlPasteValues without any luck. Anyone able to help me tweak this one a bit so it copies only values?

    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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,008
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

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

    Hi & welcome to MrExcel.
    How about
    Code:
    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Mar 2011
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Thanks Fluff.... Worked fine!

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,008
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

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

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •