Sort data by column name - VBA

[marc01]

Hi there,

I have very limited experience with macros and even less with VBA.
I am looking to sort my data based on the column, and thought If i simply recorded my steps using the record macro function in excel that this would be possible.
The problem I am encountering is that the code created is specific to the workbook "200120 Jeff V Bolton", however I would like to be able to use this macro in different worksheets. Furthermore, if this were to work on another sheet I see that the range is to 206 rows, however some sheets may have more rows, some may have less.

Any help would be much appreciated.

I have copied and pasted the code below:

Sub Macro999()
'
' Macro999 Macro
'

'
Range("A1").Select
ActiveWorkbook.Worksheets("200120 Jeffer V Bolton").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("200120 Jefff V Bolton").Sort.SortFields. _
Add2 Key:=Range("A1:M1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
CustomOrder:= _
"1st serve,2nd serve,Point won by,Serve outcome,Serve+1 Hand,Serve+1 outcome,Serve+1 Situation,Server,Side" _
, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("200120 Jeff V Bolton").Sort
.SetRange Range("A1:M206")
.Header = xlYes
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Columns("J:M").Select
Selection.Delete Shift:=xlToLeft
End Sub

 

[mumps]

I haven't tested this macro, but you will notice that I have used "ActiveSheet" and "LastRow". This macro should work on any sheet regardless of the number of rows as long as it is the active sheet.

Sub SortData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range("A1:M1"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
            "1st serve,2nd serve,Point won by,Serve outcome,Serve+1 Hand,Serve+1 outcome,Serve+1 Situation,Server,Side", DataOption:=xlSortNormal
        .SetRange Range("A1:M" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("J:M").Delete Shift:=xlToLeft
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

How about

Sub marc()
   With ActiveSheet.Sort
      .SortFields.Clear
      .SortFields.Add Range("A1:M1"), xlSortOnValues, xlAscending, _
         "1st serve,2nd serve,Point won by,Serve outcome,Serve+1 Hand,Serve+1 outcome,Serve+1 Situation,Server,Side"
      .SetRange Range("A1").CurrentRegion
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlLeftToRight
      .Apply
   End With
End Sub

 

[marc01]

I haven't tested this macro, but you will notice that I have used "ActiveSheet" and "LastRow". This macro should work on any sheet regardless of the number of rows as long as it is the active sheet.

Sub SortData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range("A1:M1"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
            "1st serve,2nd serve,Point won by,Serve outcome,Serve+1 Hand,Serve+1 outcome,Serve+1 Situation,Server,Side", DataOption:=xlSortNormal
        .SetRange Range("A1:M" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("J:M").Delete Shift:=xlToLeft
    Application.ScreenUpdating = True
End Sub

Brilliant! This works perfectly - many thanks.

 

[marc01]

How about

Sub marc()
   With ActiveSheet.Sort
      .SortFields.Clear
      .SortFields.Add Range("A1:M1"), xlSortOnValues, xlAscending, _
         "1st serve,2nd serve,Point won by,Serve outcome,Serve+1 Hand,Serve+1 outcome,Serve+1 Situation,Server,Side"
      .SetRange Range("A1").CurrentRegion
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlLeftToRight
      .Apply
   End With
End Sub

Many thanks - this does order the columns correctly as well, however the code above from mumps does also delete the final 4 columns also. I am going to play about with both codes so i can learn from them.

 

[Fluff]

Glad we could help & thanks for the feedback.