Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AdvancedFilter problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AdvancedFilter problem
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AdvancedFilter problem
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AdvancedFilter using 2 workbooks | Excel Programming | |||
AdvancedFilter | Excel Programming | |||
Advancedfilter | Excel Programming | |||
AdvancedFilter with VBA | Excel Programming | |||
AdvancedFilter in VB | Excel Programming |