VBA To Sort

[honkin]

I am trying to have a VBA macro sort a sheet based on 3 columns; A, B & C. A is Date, B is Country and C is League

I did a simple record macro, but it added a defined range and sheet name. I changed it as this will be run on a different sheet daily. The basic idea is to sort the data based on A, B & C making it ready to be copied using another macro which works fine. Up until now I have been manually doing the sorting part.

Content is from columns A to LQ, which is why I changed the recorded macro setting of Range(A1:LQ226).Select to simply A2:LQ, as the number of rows will vary daily. The first row contains headers

Here is the code generated in the macro recorder, although the sheet name was also in there originally and I removed it. It now gives a Compile error: Method or data member not found

Sub Sort_TP()
'
' Sort_TP Macro
'

'
    Range("A2:LQ").Select
    ActiveWorkbook.Worksheets.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets.Sort.SortFields.Add2 Key:= _
        Range("A2:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets.Sort.SortFields.Add2 Key:= _
        Range("B2:B"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets.Sort.SortFields.Add2 Key:= _
        Range("C2:C"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets.Sort
        .SetRange Range("A2:LQ")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

I just need to be able to sort any sheet which is opened

Thanks in advance

 

[Dossfm0q]

Hi In WorkBook Module place below code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim DataRng As Range, SrtRng As Range
Dim CLCnt As Long, RwCnt As Long

Set DataRng = Sh.UsedRange.Columns("A:C")
  

CLCnt = DataRng.Columns.Count 'Number of Columns
RwCnt = DataRng.Rows.Count    'Number of Rows

    For Cl = 1 To CLCnt 'to go through Range Columns On By on

    Set SrtRng = DataRng.Cells(1, Cl).Resize(RwCnt, 1) ' Sort Range column by column
        SrtRng.Sort Key1:=SrtRng.Cells(1, 1), Order1:=xlAscending, Header:=xlNo

    Next



End Sub

 

[Fluff]

How about

Sub honkin()
   ActiveSheet.UsedRange.Sort Range("A2"), xlAscending, Range("B2"), , xlAscending, Range("C2"), xlAscending, xlYes, , False
End Sub

 

[honkin]

Hi In WorkBook Module place below code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim DataRng As Range, SrtRng As Range
Dim CLCnt As Long, RwCnt As Long

Set DataRng = Sh.UsedRange.Columns("A:C")
 

CLCnt = DataRng.Columns.Count 'Number of Columns
RwCnt = DataRng.Rows.Count    'Number of Rows

    For Cl = 1 To CLCnt 'to go through Range Columns On By on

    Set SrtRng = DataRng.Cells(1, Cl).Resize(RwCnt, 1) ' Sort Range column by column
        SrtRng.Sort Key1:=SrtRng.Cells(1, 1), Order1:=xlAscending, Header:=xlNo

    Next



End Sub

Cheers Dossfm0q

The worksheet changes daily, so this is not possible.

Regards

 

[honkin]

How about

Sub honkin()
   ActiveSheet.UsedRange.Sort Range("A2"), xlAscending, Range("B2"), , xlAscending, Range("C2"), xlAscending, xlYes, , False
End Sub

Spot on Fluff. This works like a charm

Thanks again

 

[Fluff]

Glad we could help & thanks for the feedback.