AdvancedFilter problem

[Whit]

I am trying to get a unique list of items using an the advancedfilter method
but it keeps pasting the data to the wrong sheet. I cannot see how this is
happening as i thought that the paste range had to be on the same sheet?


ActiveSheet.Range("B1:B" & ActiveSheet.UsedRange.Rows.Count).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("M1"), Unique:=True

Cheers

Whit

 

[Dave Peterson]

You can put the filtered list on any sheet you want.

But if your code is behind a worksheet, then that unqualified range
(copytorange) will refer to the sheet that owns the code--not always the
activesheet.

I'd try this first.

ActiveSheet.Range("B1:B" & ActiveSheet.UsedRange.Rows.Count).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=activesheet.Range("M1"), Unique:=True

or

with activesheet
.Range("B1:B" & .UsedRange.Rows.Count).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=.Range("M1"), Unique:=True
end with

(to avoid some typing)

 

[Whit]

thanks it worked

You can put the filtered list on any sheet you want.

But if your code is behind a worksheet, then that unqualified range
(copytorange) will refer to the sheet that owns the code--not always the
activesheet.

I'd try this first.

ActiveSheet.Range("B1:B" & ActiveSheet.UsedRange.Rows.Count).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=activesheet.Range("M1"), Unique:=True

or

with activesheet
.Range("B1:B" & .UsedRange.Rows.Count).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=.Range("M1"), Unique:=True
end with

(to avoid some typing)