VBA - xlFilterCopy/CopyToRange - Paste Special to retain destination formatting?

[BPW]

Hi Everyone,

I'm stuck and need a little guidance. I'm attempting to copy unique values within a column of one worksheet and paste them within another worksheet (same workbook) retaining the destination sheet's formatting. Below is the VBA that pastes over the formatting.

Sub CreateUniqueList2()
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("Data Dump")
Set ws2 = Worksheets("rollups")

Dim lastrow As Long

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


ws1.Activate

ws1.Range("B3:B" & lastrow).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=ws2.Range("b4"), _
    Unique:=True
     
End Sub

Thanks in advance!
BPW

 

[Fluff]

As you have 365, why not just use this formula

=UNIQUE(FILTER('Data Dump'!B3:B1000,'Data Dump'!B3:B1000<>""))

 

[BPW]

That is a great solution, thank you! I did happen upon this awesome formula in my research but I wanted to try it this way to learn VBA. Any suggestions?

 

[Fluff]

As you have found using the advanced filter will copy everything rather than just values & I'm not sure that can be changed.

 

[Akuini]

Another option:
Before executing the filter, copy col B in ws2 and paste format to a helper column then after executing the filter, copy it back & paste format.
OR if you have another column that has the same format then you can just use that column.

 

[Fluff]

You could use the unique function in VBA like

Sub CreateUniqueList2()
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("Data Dump")
Set ws2 = Worksheets("rollups")
Dim Ary As Variant
Dim lastrow As Long

lastrow = ws1.Cells(Rows.Count, "B").End(xlUp).Row
Ary = Application.Unique(ws1.Range("B3:B" & lastrow).Value)
ws2.Range("B4").Resize(UBound(Ary)).Value = Ary
End Sub

 

[BPW]

Thanks Fluff and Akuini! I will try this out and let you know how I fare.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[BPW]

Posting a follow-up. The script you provided Fluff worked phenomenally. Thank you both for your help and guidance!

 

[Fluff]

My pleasure.