Use VBA to Copy & Paste Unique Value from One Worksheet to Another (Paste as Values)

jhiltz725 · Oct 2nd 2021

Hi there,

I need help to create a VBA code that will allow me to extract values from a list (Column Q) in sheet "LW SKU Data" and paste into another sheet "Brand Recap" to column A (starting in row A14.) I'd want to paste as values, as the list in column Q is using a VLOOKUP to find the values from a different sheet.Lookinng to paste UNNIQUE VALUES with no blanks.

Will someone please tell me the code that would allow this? I am trying to create a template for multiple users, so the values will change depending on the data that is pasted.

Thank you!

Logit · Oct 2nd 2021

You will need to edit the sheet numbers to match your workbook :

Code

Sub cpyvals2()

Dim CopyRng As Range, PasteRng As Range
Set PasteRng = Sheet2.Range("A14")

With Sheet1 'CodeName
 .Range("Q1", .Cells(Rows.Count, 17).End(xlUp)).Copy
 PasteRng.PasteSpecial xlPasteValuesAndNumberFormats
 PasteRng.PasteSpecial xlPasteFormats
 Application.CutCopyMode = False
End With

End Sub

Display More

jhiltz725 · Oct 2nd 2021

Hi,

This is working, BUT I need the values to paste WITHOUT formatting and to remove duplicates & blanks.

Thank you!

Logit · Oct 2nd 2021

Provide a sample of your workbook (no confidential data).

jhiltz725 · Oct 2nd 2021

Attached. I'm trying to get column "Q" in the "LW SKU Data" tab to paste without blanks or duplicates into column "A" (within the template starting in row "A6") in the "LW Brand Recap" tab. Brand Recap Template_Example.xlsb

vcoolio · Oct 3rd 2021

Hello JH,

Another option:-

Code

Option Explicit
Sub Uniques()


Dim c As Range, ar As Variant, var As Variant
Dim Rng As Range: Set Rng = Sheet2.Range("Q2", Sheet2.Range("Q" & Sheet2.Rows.Count).End(xlUp))


With Rng
With CreateObject("Scripting.Dictionary")
For Each c In Rng
var = .Item(c.Value)
Next c
ar = .Keys
End With
End With


Sheet1.Range("A14").Resize(UBound(ar) + 1) = Application.Transpose(ar)


End Sub

Display More

I hope that this helps.

Cheerio,

vcoolio.

jhiltz725 · Oct 3rd 2021

Yes that worked! Thank you so much!!

vcoolio · Oct 3rd 2021

You're welcome JH. I'm glad to have been able to assist.

I gather that you worked out how to change the destination target from A14 to A6 (opening post/post #5).

Good luck with your project.

Cheerio,

vcoolio.

jhiltz725 · Oct 3rd 2021

Yes - thank you very much!

**royUK** · Oct 3rd 2021

Have you got the UNIQUE Function in your formulas list?

Use VBA to Copy & Paste Unique Value from One Worksheet to Another (Paste as Values)

Participate now!

Share