How copy Criteriarange from uatofilter colun to another sheet?
 

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.

How copy Criteriarange from uatofilter colun to another 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.

How copy Criteriarange from uatofilter colun to another 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.

How copy Criteriarange from uatofilter colun to another 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.

How copy Criteriarange from uatofilter colun to another 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.

How copy Criteriarange from uatofilter colun to another 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 -