In this code to sort a table I get different errors.
In my sheet, these are the first parameters I would like to exclude.
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
I record macros and do different experiments.
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
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