Hello!
I trying to copy CriteriaRange to another sheet but i see error in me code :(
My code is

AutoFilter(2).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Worksheet(2).Range("E:E"), Unique:=true
How to copy Criteriarange to another list but so that not turn off
autofilter in main sheet.

Example file: http://aljany.com/My_Book_sample2.xls
Question: http://aljany.com/Criterias_list.jpg

"Valery" wrote:

Hello!
I trying to copy CriteriaRange to another sheet but i see error in me code :(
My code is

AutoFilter(2).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Worksheet(2).Range("E:E"), Unique:=true
How to copy Criteriarange to another list but so that not turn off
autofilter in main sheet.

Do you mean something like this?:

Sub uniques_from_E()
'
' 09/10/2007 by Pete Ashurst
'
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
ActiveCell.Range("A1:A" & _
Cells(Rows.Count, "A").End(xlUp).Row).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange _
:=ActiveCell.Offset(0, 2).Range("A1"), Unique:=True
Columns("A:B").Delete Shift:=xlToLeft
Range("A1").Select
End Sub

It will give you the list of unique values from column E of the
current sheet (assume heading is on row2, as per your example) and
sorted.

It does not include the choices "All", "Top 10", "Custom ..." which
the Autofilter pull-down shows at the top, nor "Blanks" or "Non-
blanks" shown at the bottom.

Hope this helps.

Pete

On Oct 9, 8:45 am, Valery wrote:
Hello!
I trying to copy CriteriaRange to another sheet but i see error in me code :(
My code is

AutoFilter(2).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Worksheet(2).Range("E:E"), Unique:=true
How to copy Criteriarange to another list but so that not turn off
autofilter in main sheet.

Hello Pite_UK
Thank you for code.
I tried insert your function in my example file
http://aljany.com/My_Book_sample2.xls
but this not work there
Pite_UK tell me please How correctly insert your function in my file
http://aljany.com/My_Book_sample2.xls
Because dosn't work :( and i see error :(
Can you'll be edit my file ? And give me link to them?
Thank you for you time Pite_UK

"Pete_UK" wrote:

Do you mean something like this?:

Sub uniques_from_E()
'
' 09/10/2007 by Pete Ashurst
'
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
ActiveCell.Range("A1:A" & _
Cells(Rows.Count, "A").End(xlUp).Row).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange _
:=ActiveCell.Offset(0, 2).Range("A1"), Unique:=True
Columns("A:B").Delete Shift:=xlToLeft
Range("A1").Select
End Sub

It will give you the list of unique values from column E of the
current sheet (assume heading is on row2, as per your example) and
sorted.

It does not include the choices "All", "Top 10", "Custom ..." which
the Autofilter pull-down shows at the top, nor "Blanks" or "Non-
blanks" shown at the bottom.

Hope this helps.

Pete

On Oct 9, 8:45 am, Valery wrote:
Hello!
I trying to copy CriteriaRange to another sheet but i see error in me code :(
My code is

AutoFilter(2).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Worksheet(2).Range("E:E"), Unique:=true
How to copy Criteriarange to another list but so that not turn off
autofilter in main sheet.

Hello Pite_UK
Thank you for code.
I tried insert your function in my example file
http://aljany.com/My_Book_sample2.xls
but this not work there
Pite_UK tell me please How correctly insert your function in my file
http://aljany.com/My_Book_sample2.xls
Because dosn't work :( and i see error :(
http://aljany.com/error.jpg
Can you'll be edit my file ? And give me link to them?
Thank you for you time Pite_UK

"Pete_UK" wrote:

Do you mean something like this?:

Sub uniques_from_E()
'
' 09/10/2007 by Pete Ashurst
'
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
ActiveCell.Range("A1:A" & _
Cells(Rows.Count, "A").End(xlUp).Row).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange _
:=ActiveCell.Offset(0, 2).Range("A1"), Unique:=True
Columns("A:B").Delete Shift:=xlToLeft
Range("A1").Select
End Sub

It will give you the list of unique values from column E of the
current sheet (assume heading is on row2, as per your example) and
sorted.

It does not include the choices "All", "Top 10", "Custom ..." which
the Autofilter pull-down shows at the top, nor "Blanks" or "Non-
blanks" shown at the bottom.

Hope this helps.

Pete

On Oct 9, 8:45 am, Valery wrote:
Hello!
I trying to copy CriteriaRange to another sheet but i see error in me code :(
My code is

AutoFilter(2).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Worksheet(2).Range("E:E"), Unique:=true
How to copy Criteriarange to another list but so that not turn off
autofilter in main sheet.

Just copy/paste it into a standard module - there's nothing special
about it. The sheet where your data is stored should be selected when
you run the code.

Hope this helps.

Pete

On Oct 9, 1:37 pm, Valery wrote:
Hello Pite_UK
Thank you for code.
I tried insert your function in my example filehttp://aljany.com/My_Book_sample2.xls
but this not work there
Pite_UK tell me please How correctly insert your function in my filehttp://aljany.com/My_Book_sample2.xls
Because dosn't work :( and i see error :(http://aljany.com/error.jpg
Can you'll be edit my file ? And give me link to them?
Thank you for you time Pite_UK



"Pete_UK" wrote:
Do you mean something like this?:

Sub uniques_from_E()
'
' 09/10/2007 by Pete Ashurst
'
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
ActiveCell.Range("A1:A" & _
Cells(Rows.Count, "A").End(xlUp).Row).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange _
:=ActiveCell.Offset(0, 2).Range("A1"), Unique:=True
Columns("A:B").Delete Shift:=xlToLeft
Range("A1").Select
End Sub

It will give you the list of unique values from column E of the
current sheet (assume heading is on row2, as per your example) and
sorted.

It does not include the choices "All", "Top 10", "Custom ..." which
the Autofilter pull-down shows at the top, nor "Blanks" or "Non-
blanks" shown at the bottom.

Hope this helps.

Pete

On Oct 9, 8:45 am, Valery wrote:
Hello!
I trying to copy CriteriaRange to another sheet but i see error in me code :(
My code is

AutoFilter(2).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Worksheet(2).Range("E:E"), Unique:=true
How to copy Criteriarange to another list but so that not turn off
autofilter in main sheet.- Hide quoted text -

- Show quoted text -