VBA Sort By 2 Columns On Certain Worksheets Within A Workbook

[slivesay]

I created the following code to sort one sheet by 2 columns, but I would like to change the code to include other sheets (but not all the sheets w/in the workbook). How can I do that? All the data will be in the same columns and cells on each sheet. Also, can I still use the sheet names or should the code be changed to the sheet numbers?


Sub SortColumn()

Range("B11:M123").Select
ActiveWindow.SmallScroll Down:=-102
ActiveWorkbook.Worksheets("Break").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Break").Sort.SortFields.Add Key:=Range("K11:K123") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Break").Sort.SortFields.Add Key:=Range("C11:C123") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Break").Sort
.SetRange Range("B11:M123")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

 

[igold]

If there are not too many sheets and you wanted to hard code the names of the sheets into the code you could use something like this:

Sub SortColumn()


    Dim shtnam
    Dim i As Long


    shtnam = Array("Break", "Sheet1", "Sheet2") [COLOR=#ff0000]'Hard code your sheet names here

[/COLOR]
    For i = 0 To UBound(shtnam)
        ActiveWorkbook.Worksheets(shtnam(i)).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(shtnam(i)).Sort.SortFields.Add Key:=Range("K11:K123") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        ActiveWorkbook.Worksheets(shtnam(i)).Sort.SortFields.Add Key:=Range("C11:C123") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets(shtnam(i)).Sort
            .SetRange Range("B11:M123")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Next
    
End Sub