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 <Val...@discussions.microsoft.com> 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 <Val...@discussions.microsoft.com> 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 <Val...@discussions.microsoft.com> 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 <Val...@discussions.microsoft.com> 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 <Val...@discussions.microsoft.com> 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 -