CopyToRange doesn't copy

Tjordaske

New Member
Joined
Nov 23, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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:
1637681392322.png


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:
1637681472996.png


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

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

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
Back
Top