I have an ineffective and long code that needs optimising or trimming

Question

Well this is obviously part of the code but this part I keep getting lots of different error and as a beginner I don't really understand how I can adress them.

I'm in my workbook and sheet, so these are the first parameters I would like to exclude here but I get finding my self generating error rather then shorter code and so I'm asking for some help/guidens.

Sub SortHeaderSVA()

    Worksheets("PR11_P3").ListObjects("PR11_P3_Tabell").ListColumns("SVA").Range.Cells(1).Select
        'cant do anything of the soriting or so if I dont do above which is, well has to be a simpler way to select a header in a table that is in the current sheet and active workbook..?

    ActiveWorkbook.Worksheets("PR11_P3").ListObjects("PR11_P3_Tabell").Sort. _
        SortFields.Clear
        'also wierd, why dose the sort needs to be cleard first if there is no value earlier?

    ActiveWorkbook.Worksheets("PR11_P3").ListObjects("PR11_P3_Tabell").Sort. _
        SortFields.Add Key:=Range("PR11_P3_Tabell[[#All],[SVA]]"), SortOn:= _
        xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

     With ActiveWorkbook.Worksheets("PR11_P3").ListObjects("PR11_P3_Tabell").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End Sub

Lots of different ways to select and so but I probably don't know proper commands for typing only in VBA so I record macros and do different experiments.

Answer 1

Don't select Objects unless absolutely necessary. Remove this line:

Worksheets("PR11_P3").ListObjects("PR11_P3_Tabell").ListColumns("SVA").Range.Cells(1).Select

SortFields.Clear is used to ensure that the previous settings are cleared. After all we are adding sorts to a collection. You can test this by commenting out the SortFields.Clear line and running it several times. Next from the Ribbon open the Data Tab and choose Sort. You'll see that there are multiple sorts that are going to be ran whenever you call the .Sort method.

Public Sub SortHeaderSVA()

    With PR11_P3_Tabell.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=PR11_P3_Tabell.ListColumns("SVA").Range, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Public Function wsPR11_P3() As Worksheet
    Set wsPR11_P3 = ThisWorkbook.Worksheets("PR11_P3")
End Function

Public Property Get PR11_P3_Tabell() As ListObject
    Set PR11_P3_Tabell = wsPR11_P3.ListObjects("PR11_P3_Tabell")
End Property

Addendum

My original code declared the ListObject as a Property of the module. The code is auto generated from a code snippet that I have. I usually change the method signature from a Property to a Function for my post. Like this:

Public Function  PR11_P3_Tabell() As ListObject
    Set PR11_P3_Tabell = wsPR11_P3.ListObjects("PR11_P3_Tabell")
End Function 

Answer 2

Sort an Excel Table

Option Explicit

Sub SortHeaderSVA()
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Worksheets("PR11_P3")
    Dim tbl As ListObject: Set tbl = ws.ListObjects("PR11_P3_Tabell")
    Dim tlc As ListColumn: Set tlc = tbl.ListColumns("SVA")
    
    With tbl.Sort
        .SortFields.Clear
        .SortFields.Add _
            Key:=tlc.Range, _
            SortOn:=xlSortOnValues, _
            Order:=xlDescending, _
            DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End Sub

Sub SortHeaderSVANoVariables()
    
    With ThisWorkbook.Worksheets("PR11_P3").ListObjects("PR11_P3_Tabell").Sort
        .SortFields.Clear
        .SortFields.Add _
            Key:=.Parent.ListColumns("SVA").Range, _
            SortOn:=xlSortOnValues, _
            Order:=xlDescending, _
            DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End Sub