Button Sort Code

[bpgolferguy]

Hi, I currently have this code in a button:

Private Sub TourPlayers_Click()
    With Workbooks("MemberDatabase.xls").Worksheets("TourPlayers")
    .Range("A2:E300").ClearContents
    End With
    'First Name
    With Workbooks("MemberDatabase.xls").Worksheets("Members")
    .Range("A4").Resize(.Range("AD1").Value).Copy
    End With
    With Workbooks("MemberDatabase.xls").Worksheets("TourPlayers")
    .Range("C2").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    End With
    'Last Name
    With Workbooks("MemberDatabase.xls").Worksheets("Members")
    .Range("B4").Resize(.Range("AD1").Value).Copy
    End With
    Worksheets("TourPlayers").Range("D2").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    'KJGT Division
    With Workbooks("MemberDatabase.xls").Worksheets("Members")
    .Range("D4").Resize(.Range("AD1").Value).Copy
    End With
    Worksheets("TourPlayers").Range("E2").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    'M/W Division
    With Workbooks("MemberDatabase.xls").Worksheets("Members")
    .Range("F4").Resize(.Range("AD1").Value).Copy
    End With
    Worksheets("TourPlayers").Range("F2").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    With Workbooks("MemberDatabase.xls").Worksheets("TourPlayers")
    .Range("A2:F400").Select
    Selection.Sort Key1:=Range("F2"), Order1:=xlAscending, Key2:=Range("E2") _
    , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom
    Range("AD2").Select
    End With
End Sub

The very last part where I sort is what's giving me fits. Starting with ".Range("A2:F400") is what it highlights and gives me an error when I click the button. Anyone know why?

 

[bpgolferguy]

The error code tells me "select method of range class failed."

 

[Norie]

Why not just remove the Select/Selection?

    With Workbooks("MemberDatabase.xls").Worksheets("TourPlayers")
        .Range("A2:F400").Sort Key1:=.Range("F2"), Order1:=xlAscending, Key2:=.Range("E2") _
                                                                              , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
                               False, Orientation:=xlTopToBottom
        Application.Goto .Range("AD2")
    End With

 

[bpgolferguy]

Perfect! Thank you!

 

[Marc]

Trying to delete my post.

 

[bpgolferguy]

Yes, right now in the design phase I haven't narrowed down exactly how many rows may or may not be populated. Once I get closer to being finished with it, I will know and can therefore make all of those figures consistent. Thanks!

 

[bpgolferguy]

Hi, I have another one that is causing me problems and not working.....this is the part of the code it highlights when I click the button

With Workbooks("MemberDatabase.xls").Worksheets("TourPlayers")
.Range("A2:F400").Sort Key1:=.Range("E2"), Order1:=xlAscending, Key2:=Range("F2") _
, Order2:=xlAscending, Key3:=Range("G2") _
, Order3:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

It says the sort reference is not valid.

 

[bpgolferguy]

I've never sorted by through 3 different criteria, so I'm not sure why it's doing that.

 

[shg]

.Range("A2:F400").Sort Key1:=[SIZE=5][COLOR=#ff0000].[/COLOR][/SIZE]Range("E2"), Order1:=xlAscending, _
                       Key2:=[SIZE=5][COLOR=red].[/COLOR][/SIZE]Range("F2"), Order2:=xlAscending, _
                       Key3:=[SIZE=5][COLOR=#ff0000].[/COLOR][/SIZE]Range("G2"), Order2:=[COLOR=red]????[/COLOR]

 

[Norie]

I don't think it's the 3 criteria causing the problems, I think it might be some missing dots.

Try sticking a dot before Range for all the keys, eg Key1:=.Range("F2")