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

BPW

New Member
Joined
Mar 7, 2013
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
As you have 365, why not just use this formula
Excel Formula:
=UNIQUE(FILTER('Data Dump'!B3:B1000,'Data Dump'!B3:B1000<>""))
 
  • Like
Reactions: BPW

BPW

New Member
Joined
Mar 7, 2013
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
As you have found using the advanced filter will copy everything rather than just values & I'm not sure that can be changed.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 
  • Like
Reactions: BPW

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
You could use the unique function in VBA like
VBA Code:
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
 
  • Like
Reactions: BPW
Solution

BPW

New Member
Joined
Mar 7, 2013
Messages
24
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

BPW

New Member
Joined
Mar 7, 2013
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Posting a follow-up. The script you provided Fluff worked phenomenally. Thank you both for your help and guidance!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
My pleasure.
 
  • Like
Reactions: BPW

Watch MrExcel Video

Forum statistics

Threads
1,130,129
Messages
5,640,295
Members
417,135
Latest member
zeusmining

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top