Excel VBA Advanced Find Unique Values from Table to separate worksheet

init

Public ws_unique As Worksheet
Public ws_copy_to As Worksheet
Public ws_table1 As Worksheet
  
Public lr_table1 As Long
Public lr_unique As Long

Sub init()
  Set ws_unique = Sheets("unique")
  Set ws_copy_to = Sheets("copy_to")
  Set ws_table1 = Sheets("table1")
  
  lr_table1 = Range("A" & Rows.Count).End(xlUp).Row
End Sub

select_table_clear_filters_and_clear_unique

Sub select_table_clear_filters_and_clear_unique()
  ws_unique.Cells.Clear
  ws_table1.Select
  With ActiveSheet
    .AutoFilterMode = False
    .Range("table1[[#Headers]]").AutoFilter
  End With
End Sub

unqiue_values_to_worksheet

Sub unqiue_values_to_worksheet()
  
  'this assumes you have started with a workbook with three worksheets named "table1", "unique" and and "copy_to" 
  'the worksheet named table has a table of data that has been formatted as an Excel table and named 'table1'
  
  call init.init
  ws_copy_to.Range("A2:Z1000000").Clear
  Application.Calculation = xlCalculationManual
  Call select_table1_clear_filters_and_clear_unique

  Range("A1:A" & lr_table1).Copy
  ws_unique.Select
  ws_unique.Range("A1").PasteSpecial
  ws_unique.Range("A1:A" & lr_table1).AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=ws_copy_to.Range("A2"), UNIQUE:=True