.AdvancedFilter (Alphjabetical ordering?)

[Dancey_]

Hey guys,

I am using the below to copy a row of data (Staff Names) across to a sheet so I can use the data in a drop down selection list that will be used to generate a sales report.

Does anybody know how to leave the source data as is but make the copied data alphabetical order?

My code is:

Private Sub Worksheet_Change(ByVal Target As Range)
Set shtReport = Sheets("Reporting")
    Columns("C:C").Select
    Columns("C:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=shtReport.Range("S:S"), Unique:=True
End Sub

Thanks!

 

[GlennUK]

Sort the copied data!

 

[Dancey_]

Thanks for the idea but I need to be able to do this automatically.

The data will be used as a source for a data validation in cell drop down list.

Cheers

 

[GlennUK]

I meant that you should sort it in the macro. Like:

shtReport.Range("S1", shtReport.Range("S65536").End(xlUp)).Sort _
    Key1:=Range("S1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

 

[Dancey_]

Thanks for the code dude. I have changed my sub slightly so it now runs on the press of a button, I added your code to the bottom, but it doesn't work. I get 400 in a pop up, the first part of the code is working as it is copying my list, but it isn't sorting it.

No doubt I have done something stupid, can you please help?

Sub GenerateUsers()
Set sht1 = Sheets("Sales by Agent")
Sheets("Sales").Activate
    Columns("C:C").Select
    Columns("C:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=sht1.Range("S:S"), Unique:=True
    sht1.Range("S1", sht1.Range("S65536").End(xlUp)).Sort _
    Key1:=Range("S1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
End Sub

 

[GlennUK]

How about activating sht1 before the sort?

 

[Dancey_]

I tried that before, I have tried this:

Sub GenerateUsers()
Set sht1 = Sheets("Sales by Agent")
Sheets("Sales").Activate
    Columns("C:C").Select
    Columns("C:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=sht1.Range("S:S"), Unique:=True
sht1.Activate
    sht1.Range("S1", sht1.Range("S65536").End(xlUp)).Sort _
    Key1:=Range("S1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
End Sub

and this:

Sub GenerateUsers()
Set sht1 = Sheets("Sales by Agent")
Sheets("Sales").Activate
    Columns("C:C").Select
    Columns("C:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=sht1.Range("S:S"), Unique:=True
Sheets("Sales by Agent").Activate
    sht1.Range("S1", sht1.Range("S65536").End(xlUp)).Sort _
    Key1:=Range("S1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
End Sub

Neither seems to work.

Thanks for the help

 

[GlennUK]

What code do you get if you macro record yourself sorting the range? That may be a better starting place than my code.

 

[Dancey_]

I get the following code:

Sub SortAZ()
'
' SortAZ Macro
'

'
    Columns("S:S").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Sales by Agent").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sales by Agent").AutoFilter.Sort.SortFields.Add Key _
        :=Range("S1:S9"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Sales by Agent").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Which I dropped in with mine:

Sub GenerateUsers()
Set sht1 = Sheets("Sales by Agent")
Sheets("Sales").Activate
    Columns("C:C").Select
    Columns("C:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=sht1.Range("S:S"), Unique:=True
Sheets("Sales by Agent").Activate
 Columns("S:S").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Sales by Agent").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sales by Agent").AutoFilter.Sort.SortFields.Add Key _
        :=Range("S1:S9"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Sales by Agent").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Which gave the same error.

So, I did this:

Sub GenerateUsers()
Set sht1 = Sheets("Sales by Agent")
Sheets("Sales").Activate
    Columns("C:C").Select
    Columns("C:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=sht1.Range("S:S"), Unique:=True
Call SortAZ
End Sub

Which runs this when it is done:

Sub SortAZ()
    Sheets("Sales by Agent").Activate
    Columns("S:S").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Sales by Agent").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sales by Agent").AutoFilter.Sort.SortFields.Add Key _
        :=Range("S1:S9"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Sales by Agent").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

AND IT WORKS Thanks for your help!!

 

[GlennUK]

Hooray!