Sort code in button not working

[bpgolferguy]

Hi, I have this code inside a button on workbook:

Private Sub CommandButton5_Click()
Range("C5:F300").Copy
Sheets("Overall").Range("B3").PasteSpecial Paste:=xlValues
With Sheets("TourSBAmMen")
Application.CutCopyMode = False
End With
Sheets("Overall").Range("B3:E300").Sort Key1:=Range("D3"), Order1:=xlDescending, Key2:=Range("E3"), Order2:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
Range("A2").Select
End Sub

It works okay, except it doesn't sort the range I've chosen on sheet "Overall". Anyone know why? Thanks!

 

[VoG]

Try

Private Sub CommandButton5_Click()
Range("C5:F300").Copy
Sheets("Overall").Range("B3").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
With Sheets("Overall")
    .Range("B3:E300").Sort Key1:=.Range("D3"), Order1:=xlDescending, Key2:=.Range("E3"), Order2:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
End With
End Sub

 

[Norie]

Have those dots gone astray again?

Don't know if this helps, but you don't have to use With all the time.

Private Sub CommandButton5_Click()
 
Dim wsOveralll As Worksheet
Dim wsTour As Worksheet
 
    Set wsOverAll = Sheets("Overall")
 
    Set wsTour = Sheets("TourSBAmMen")
 
    wsTour.Range("C5:F300").Copy
 
    wsOverAll.Range("B3").PasteSpecial Paste:=xlValues
 
    Application.CutCopyMode = False
 
    wsOverAll.Range("B3:E300").Sort Key1:=wsOverAll.Range("D3"), Order1:=xlDescending, Key2:=wsOverAll.Range("E3"), Order2:=xlAscending, Header:=xlGuess, _
                                    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                                    DataOption1:=xlSortNormal
 
End Sub

 

[bpgolferguy]

Thanks! One last thing....is there a reason it's not including row 3 in this sort? Either I don't have something right, or it is sorting from row 4 down....any thoughts?

 

[VoG]

Try Header:=xlNo

 

[bpgolferguy]

Genuis! It worked! Thank you as always!