Sorting VBA

[Jamm_027]

I recorded a macro that sorts exactly like I need. Now I want this Macro to work no matter what Sheet within the workbook I am using. Not just the sheet named sheet1. I realize I could change the sheet name within the code but I dont want to repeat that over and over. And I know there is a way to clean this code up?????

 Range("C6:H300").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("H6:H300"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D6:D300"), _
       SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
       .SetRange Range("C6:H300")
      .Header = xlGuess
     .MatchCase = False
    .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("C3").Select

 

[shg]

To sort the active worksheet,

    With ActiveSheet
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range("H6"), _
                             SortOn:=xlSortOnValues, _
                             Order:=xlAscending, _
                             DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=.Range("D6"), _
                             SortOn:=xlSortOnValues, _
                             Order:=xlAscending, _
                             DataOption:=xlSortNormal
        .Sort.SetRange .Range("C6:H300")
 
        With .Sort            
            .Header = [COLOR=red]xlGuess[/COLOR]
            .MatchCase = False
            .Orientation = xlTopToBottom
            .Apply
        End With
    End With

You should change xlGuess to xlYes or xlNo according to whether your data has headers.

 

[Jamm_027]

Thanks SHG,

I am getting a run time error 450: Wrong number of arguments or invalid property assignment

then when I debug it has the row highlighted:

.SetRange .Range("C6:H300")

 

[shg]

[COLOR=red].Sort[/COLOR].SetRange .Range("C6:H300")

I just ran the code copied directly from the post - try again.

 

[Jamm_027]

I am getting a run time error 450 still saying wrong number of arguments???

Sub Sort_Tasks()

Application.ScreenUpdating = False
    
    With ActiveSheet
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range("H6"), _
                             SortOn:=xlSortOnValues, _
                             Order:=xlAscending, _
                             DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=.Range("D6"), _
                             SortOn:=xlSortOnValues, _
                             Order:=xlAscending, _
                             DataOption:=xlSortNormal
        With .Sort
            .SetRange.Range ("C6:H300")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .Apply
        End With
    End With

Application.ScreenUpdating = True

End Sub