Macro for Unique Values to be shown on separate tab

[JaimeDee]

Please can anyone help. I am super new to VBA so apologies if this is too basic a question. I want to set up a macro button on my tool bar that copys a column that I highlight into another tab and then removes duplicates as well as removing any blanks to leave me with unique values. When I run the below, it copys the header but not the data within the whole column. This means after it has done its cleanse, I am just left with the header of the column on its own on a separate sheet? What am I doing wrong?

I intend to store this in my personal macro workbook so I can run it on any document. Not sure if that makes a difference at all?!

Dim rSelection As Range
Dim ws As Worksheet

 'Check that a range is selected
  If TypeName(Selection) <> "Range" Then
    MsgBox "Please select a range first.", vbOKOnly, "List Unique Values Macro"
    Exit Sub
  End If
 
  'Store the selected range
  Set rSelection = Selection

  'Add a new worksheet
  Set ws = Worksheets.Add
 
  'Copy/paste selection to the new sheet
  rSelection.Copy
 
  With ws.Range("A1")
    .PasteSpecial xlPasteValues
    .PasteSpecial xlPasteFormats
    '.PasteSpecial xlPasteValuesAndNumberFormats
  End With
 
  'Remove duplicates
  ws.UsedRange.RemoveDuplicates Columns:=1, Header:=xlGuess
 
  'Remove blank cells (optional)
  On Error Resume Next
    ws.UsedRange.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlShiftUp
  On Error GoTo 0
 
  'Autofit column
  ws.Columns("A").AutoFit
 
  'Exit CutCopyMode
  Application.CutCopyMode = False
  
End Sub

 

[RoryA]

What did you select before you ran the macro? All the data or just the first cell in the column?

 

[JaimeDee]

What did you select before you ran the macro? All the data or just the first cell in the column?

Hi, Thank you for such a quick reply. This is my first post on the forum.

I selected the column letter (my data isn't in a formal excel "table" its just data in cells if you know what I mean. So I just clicked on the column label (which was column F) so the whole column was highlighted