VBA To Sort

honkin

Board Regular
Joined
Mar 20, 2012
Messages
145
Office Version
  1. 2016
Platform
  1. MacOS
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



Code:
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Dossfm0q

Active Member
Joined
Mar 9, 2009
Messages
399
Hi In WorkBook Module place below code
VBA 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
 

Attachments

  • WB.jpg
    WB.jpg
    34.3 KB · Views: 4

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,023
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub honkin()
   ActiveSheet.UsedRange.Sort Range("A2"), xlAscending, Range("B2"), , xlAscending, Range("C2"), xlAscending, xlYes, , False
End Sub
 

honkin

Board Regular
Joined
Mar 20, 2012
Messages
145
Office Version
  1. 2016
Platform
  1. MacOS
Hi In WorkBook Module place below code
VBA 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

Board Regular
Joined
Mar 20, 2012
Messages
145
Office Version
  1. 2016
Platform
  1. MacOS
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,023
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,836
Messages
5,544,600
Members
410,625
Latest member
Hemantjain86
Top