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 |
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 |
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 |
All times are GMT +1. The time now is 07:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com