Slight Amendment To Code

[Dazzawm]

I did the code below by recording and want a bit of it amended please. Where it says worksheets 'New' I would like that changed to the active sheet and where it says the range C2:C1262, A1:AY1262 etc I would like that changed to the used range of rows and columns as it will change each sheet I use it on.

Thanks.

Range("A1").Select
    ActiveWorkbook.Worksheets("New").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("New").Sort.SortFields.Add2 key:=Range("C2:C1262"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("New").Sort.SortFields.Add2 key:=Range("A2:A1262"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("New").Sort
        .SetRange Range("A1:AY1262")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[Alex Blakenburg]

If you are happy to use Row 1 to determine the last column and Column A to determine the last row, then this might work for you.

Sub SortActiveSheet

    Dim wb As Workbook
    Dim wsAct As Worksheet
    Dim rng As Range
    Dim lr As Long, lc As Long
    
    Set wb = ActiveWorkbook
    Set wsAct = wb.ActiveSheet
    
    With wsAct
        lr = .Cells(Rows.Count, "A").End(xlUp).Row
        lc = .Cells(1, Columns.Count).End(xlToLeft).Column
        Set rng = .Range(.Cells(1, "A"), .Cells(lr, lc))
    End With
    
    wsAct.Range("A1").Select
    wsAct.Sort.SortFields.Clear
    wsAct.Sort.SortFields.Add2 Key:=rng.Cells(1, 3), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    wsAct.Sort.SortFields.Add2 Key:=rng.Cells(1, 1), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With wsAct.Sort
        .SetRange rng
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End Sub

 

[Dazzawm]

If you are happy to use Row 1 to determine the last column and Column A to determine the last row, then this might work for you.

Sub SortActiveSheet

    Dim wb As Workbook
    Dim wsAct As Worksheet
    Dim rng As Range
    Dim lr As Long, lc As Long
   
    Set wb = ActiveWorkbook
    Set wsAct = wb.ActiveSheet
   
    With wsAct
        lr = .Cells(Rows.Count, "A").End(xlUp).Row
        lc = .Cells(1, Columns.Count).End(xlToLeft).Column
        Set rng = .Range(.Cells(1, "A"), .Cells(lr, lc))
    End With
   
    wsAct.Range("A1").Select
    wsAct.Sort.SortFields.Clear
    wsAct.Sort.SortFields.Add2 Key:=rng.Cells(1, 3), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    wsAct.Sort.SortFields.Add2 Key:=rng.Cells(1, 1), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With wsAct.Sort
        .SetRange rng
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
End Sub

Thanks. I meant to say this bit of recorded code is added to other code at the bottom so I just need that bit changed if possible?

 

[Alex Blakenburg]

I don't understand what you are saying.
That code will work whether you put it at bottom of your existing code or if in your existing code you just use Call SortActiveSheet.

 

[Dazzawm]

If you are happy to use Row 1 to determine the last column and Column A to determine the last row, then this might work for you.

Sub SortActiveSheet

    Dim wb As Workbook
    Dim wsAct As Worksheet
    Dim rng As Range
    Dim lr As Long, lc As Long
   
    Set wb = ActiveWorkbook
    Set wsAct = wb.ActiveSheet
   
    With wsAct
        lr = .Cells(Rows.Count, "A").End(xlUp).Row
        lc = .Cells(1, Columns.Count).End(xlToLeft).Column
        Set rng = .Range(.Cells(1, "A"), .Cells(lr, lc))
    End With
   
    wsAct.Range("A1").Select
    wsAct.Sort.SortFields.Clear
    wsAct.Sort.SortFields.Add2 Key:=rng.Cells(1, 3), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    wsAct.Sort.SortFields.Add2 Key:=rng.Cells(1, 1), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With wsAct.Sort
        .SetRange rng
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
End Sub

Thanks that worked, I got a little confused as I had other dims in my existing code. Much obliged squire.

 

[Alex Blakenburg]

No problem. If you want to show me your code, I can see if I can use your existing variables.

 

[Dazzawm]

No problem. If you want to show me your code, I can see if I can use your existing variables.

Thanks but no need, works fine.