CopyToRange doesn't copy

[Tjordaske]

Hi all,

I'm having an Excel and I want to create a list of all unique values in Column A and B and copy them to a new sheet.

This is my current Excel:

This is my code:
Sub CreateUniqueList()
Dim lastrow As Long
Dim ws As String

ws = ActiveSheet.Name
Sheets.Add.Name = "mysheet"

Sheets(ws).Range("A:B").EntireColumn.AdvancedFilter , _
Action:=xlFilterCopy, _
CopyToRange:=Sheets("mysheet").Range("A1"), _
Unique:=True

End Sub

After executing this code I get a new sheet called "mysheet" but it's empty:

What's wrong with the VBA code? Or why is this sheet empty?

 

[DanteAmor]

Hi and welcome to MrExcel.

The advanced filter requires headers. Add headings to cells A1 and B1 of the active sheet and run the macro again.

 

[Tjordaske]

Hi @DanteAmor,
Thank you for your answer! Indeed, adding headings resolves the problem. Do you know where to find this kind of information? Where I can find information and conditions about each action?

As a result I'm getting know each unique combination of row 1 and 2, but how can I make a list of all unique values?

 

[DanteAmor]

Check this out:

Excel Advanced Filter Intro - Clear Steps, Videos, Examples

How to use Advanced Filter in Excel with criteria, send data to different sheet, or filter in place. Short videos, clear examples, written steps, workbook

www.contextures.com

You can use the Remove duplicates functionality