VBA Sort

[bmkelly]

Hello,

I am trying to sort my worksheet with the follow code and I keep getting an error "This formula is missing a range reference or a defined name." on this line of my code:

Range("A1").CurrentRegion.Sort Key1:=("Absolute Value"), Order1:=xlDescending, Header:=xlYes

Here is my entire code:

With ActiveSheet.Sort
        .SortFields.Clear
            Range("A1").CurrentRegion.Sort Key1:=("Description"), Order1:=xlAscending, Header:=xlYes
            Range("A1").CurrentRegion.Sort Key1:=("Absolute Value"), Order1:=xlDescending, Header:=xlYes
            Range("A1").CurrentRegion.Sort Key1:=("Transaction Code"), Order1:=xlAscending, Header:=xlYes
            Range("A1").CurrentRegion.Sort Key1:=("QuarterSerial"), Order1:=xlAscending, Header:=xlYes

Ideally if I were to manuall sort this, this is how I would do it but I would like to automate it as much as possible

 

[Logit]

I suspect the different Column Headers are not all located in Col A ?

Your sort code all refers to Range ("A1").
And the code also refers to the same Key Number (1).

Here is a resource : VBA Sort Range | (Descending, Multiple Columns, Sort Orientation

 

[bmkelly]

I suspect the different Column Headers are not all located in Col A ?

Your sort code all refers to Range ("A1").
And the code also refers to the same Key Number (1).

Here is a resource : VBA Sort Range | (Descending, Multiple Columns, Sort Orientation

Thanks, I will take a look at those links. You are correct each of the column headers are in different columns of the spreadsheet. Ideally I would like for the code to find the column header and sort 4 total times (each with a different column header).

I had followed this video as my guide

 

[Fluff]

@Logit that link is related to Range.Sort which can only handle 3 keys, whereas the OP is trying to do a Worksheet.Sort which can handle more keys.

 

[Logit]

If you are wanting to sort all columns ... here is one way to do it. Obviously this example is not finalized for your number of columns.
Play with it and see if you can correctly edit it for your workbook.

Option Explicit

Sub SortingColumnsInRange()
    ' Selecting used range
    Range("A:F").Select

    
    Selection.Columns.Sort key1:=Columns("A"), Order1:=xlAscending, Key2:=Columns("B"), Order2:=xlAscending, key3:=Columns("C"), Order3:=xlAscending, Header:=xlYes
End Sub

 

[bmkelly]

If you are wanting to sort all columns ... here is one way to do it. Obviously this example is not finalized for your number of columns.
Play with it and see if you can correctly edit it for your workbook.

Option Explicit

Sub SortingColumnsInRange()
    ' Selecting used range
    Range("A:F").Select

   
    Selection.Columns.Sort key1:=Columns("A"), Order1:=xlAscending, Key2:=Columns("B"), Order2:=xlAscending, key3:=Columns("C"), Order3:=xlAscending, Header:=xlYes
End Sub

View attachment 57903

Thanks, I tried to mess around with it and caved in to just using the recording macro function

    ActiveWorkbook.Worksheets("Transactions").ListObjects("TransactionTable").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Transactions").ListObjects("TransactionTable").Sort. _
        SortFields.Add2 Key:=Range("TransactionTable[QuarterSerial]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Transactions").ListObjects("TransactionTable").Sort. _
        SortFields.Add2 Key:=Range("TransactionTable[Transaction Code]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Transactions").ListObjects("TransactionTable").Sort. _
        SortFields.Add2 Key:=Range("TransactionTable[Absolute Value]"), SortOn:= _
        xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Transactions").ListObjects("TransactionTable").Sort. _
        SortFields.Add2 Key:=Range("TransactionTable[Description]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Transactions").ListObjects("TransactionTable"). _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        
    End With