excel - VBA Advanced filter unique values and copy to another sheet -

i've tried number of methods filter column unique values (including blanks , blanks space inside). want filter these values , paste them column in sheet.

sub filter_2()     sheets("icm flags")          .columns("a").advancedfilter action:=xlfiltercopy, copytorange:=sheets("flag update (2)").range("a2"), unique:=true      end end sub 

the method above works .range("a2") if try use .range("a1") runtime error 1004 advanced filter method of class failed.

i runtime error 1004 advanced filter method of class failed method below.

sub unique_filter()                 sheets("icm flags").columns("a").advancedfilter _                      action:=xlfiltercopy, _                         copytorange:=sheets("flag update (2)").range("a1"), unique:=true  end sub 

for both of above methods , edits davesexcel answer:

sub advfilter() dim ws worksheet, sh worksheet dim rng range, frng range  set sh = sheets("icm flags") set frng = sh.range("a:a") set ws = sheets("flag update (2)") set rng = ws.range("a1")  'frng.advancedfilter action:=xlfiltercopy, copytorange:=rng, unique:=true frng.advancedfilter action:=xlfilterinplace, unique:=true  end sub 

the method works filterinplace not copytorange

when ran code, worked no errors on end.

this same code using variables , might work better you.

sub advfilter()     dim ws worksheet, sh worksheet     dim rng range, frng range      set ws = sheets("flag update (2)")     set rng = ws.range("a1")     set sh = sheets("icm flags")     set frng = sh.range("a:a")      frng.advancedfilter action:=xlfiltercopy, copytorange:=rng, unique:=true  end sub