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

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 wrote:
>
> 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

thanks it worked

"Dave Peterson" wrote:

> 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 wrote:
> >
> > 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
>