Sorting tables based on two columns on multiple sheets with vba

This code should do what you want. Before trying it please review the two column addresses.

Sub Formatting()

    Dim Sht     As Worksheet
    Dim Tbl     As ListObject
    For Each Sht In Worksheets
        If Sht.Name <> "Original" Then
            Set Tbl = Sht.ListObjects(1)
            With Tbl.Sort.SortFields
                    .Add Key:= Tbl.DataBodyRange.Columns(3), _
                         SortOn:=xlSortOnValues, _
                         Order:=xlDescending, _
            End With
            With Tbl.Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
            End With
            Tbl.DataBodyRange.AutoFilter 9, "=FAIL"
        End If
    Next Sht
End Sub

The code presumes that your tables start in column 1 (that is column A). Tbl.DataBodyRange.Columns(3) specifies the 3rd column in the table. If your table starts at column A then column T would be 20 and the 3 in my code must be replaced with 20. Similarly, Tbl.DataBodyRange.AutoFilter 9, "=FAIL" speficies column I only if the table’s first column is A.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top