Issues with Copy/Paste Unique Values

[MarsBars]

I am having trouble with a pretty basic function. I need to copy everything in Column A (header excluded) and paste the unique values in column A in a different sheet. I chose to try to paste all of the values and then remove all of the duplicates. If there is an easier way to do this, please let me know.

    Sheets("Raw Data").Range("A:A").Copy
    
    Sheets("All Materials").Range("A3:A").Select
    ActiveRange.Paste
    Application.CutCopyMode = False
    ActiveSheet.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlYes

I am getting the "application-defined or object-defined error" message and the break is occuring at this line:

 Sheets("All Materials").Range("A3:A").Select

 

[NewOrderFac33]

The following seems to work:

Sub AAA()
    Sheets("Raw Data").Range("A:A").Copy
    Sheets("All Materials").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveSheet.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

You have to paste into A1 as you're copying the whole column - any other row throws up an error.

Hope this helps.

Pete

 

[JLGWhiz]

this will also work

    Sheets("All Materials").Range("A3:A").PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    ActiveSheet.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlYes

BTW, when using Select and Activate with Sheets and Ranges, the Sheet and ranges have to be on separate lines and each selected separately. Sheet first, then the range. Don't ask why.

 

[MarsBars]

Pete, Thank you. That worked well. I assumed it was just something small like that.

JLG, Thanks for compressing it a little. I assumed there was a way to do it without all of the selecting and activating, but I didn't know the syntax. The only problem I had with that one is that it still threw the error code when I tried to use "A3:A". It worked fine when I changed it to "A:A". That's not a big problem though. I can work around it.

Thanks for the help and explainations guys.

 

[JLGWhiz]

Pete, Thank you. That worked well. I assumed it was just something small like that.

JLG, Thanks for compressing it a little. I assumed there was a way to do it without all of the selecting and activating, but I didn't know the syntax. The only problem I had with that one is that it still threw the error code when I tried to use "A3:A". It worked fine when I changed it to "A:A". That's not a big problem though. I can work around it.

Thanks for the help and explainations guys.

The "A3:A" was copied from your original post and I should have caught it. Happy to help when I can.
Regards, JLG