Excel: VBA Using Listobjects table names as reference for sorting

userhILl1 0
Viewed 56

I am trying to create several tables one in every sheet which loops through with a rep.

The creation of the tables works and the naming of each table created also works.

What I cannot seem to get to work is adress that created table and to sort the tables xlDescending on the first column of that table.

Here is my code so far. Without the sorting included. Thanks for any advice so far!

D.

Sub TabelleEinzeln()

For rep = 5 To 9

Dim SortOrder As Integer
Dim sheet_name As String
sheet_name = Sheets("Admin").Range("H" & rep).Value
table_name = Sheets("Admin").Range("I" & rep).Value
     
    Sheets(sheet_name).Select

    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$8:$K$2276"), , xlYes).Name = _
    table_name

'Here is where I would like to select ListObjects(table_name).sort..... etc. But it gives me an out of boundry error.
     
 Next rep
 MsgBox ("Sorted")
End Sub
vba loops excel
1 Answers

If you assign a name to a table/listobject you cannot be certain if the listobject will really get the name. In case a listobject with this name already exists Excel will add some kind of appendix or number.

It is also not neccessary to know the name in order to access the listobject. My suggestion for a first fix of your code would be

Sub TabelleEinzeln()
    Dim rep As Long
    Dim table_name As String
    For rep = 5 To 9

        Dim SortOrder As Integer
        Dim sheet_name As String
        sheet_name = Sheets("Admin").Range("H" & rep).Value
        table_name = Sheets("Admin").Range("I" & rep).Value
     
        Sheets(sheet_name).Select
        
        Dim lo As ListObject
        Set lo = ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$8:$K$2276"), , xlYes)
         
        ' Example how to sort
        With lo.Sort
            .SortFields.Clear
            .SortFields.Add Key:=lo.DataBodyRange.Columns(1), SortOn:=xlSortOnValues, Order:=xlDescending
            .Header = xlYes
            .Apply
        End With

        'Here is where I would like to select ListObjects(table_name).sort..... etc. But it gives me an out of boundry error.
     
    Next rep
    MsgBox ("Sorted")
End Sub

You also do not need to Select a worksheet. Then the code would be like that

Sub TabelleEinzeln()
    Dim rep As Long
    Dim table_name As String
    For rep = 5 To 9

        Dim SortOrder As Integer
        Dim sheet_name As String
        sheet_name = Sheets("Admin").Range("H" & rep).Value
        table_name = Sheets("Admin").Range("I" & rep).Value
        
        'Sheets(sheet_name).Select
        Dim ws As Worksheet
        Set ws = Worksheets(sheet_name)
        
        Dim lo As ListObject
        Set lo = ws.ListObjects.Add(xlSrcRange, ws.Range("$A$8:$K$2276"), , xlYes)
        
        With lo.Sort
            .SortFields.Clear
            .SortFields.Add Key:=lo.DataBodyRange.Columns(1), SortOn:=xlSortOnValues, Order:=xlDescending
            .Header = xlYes
            .Apply
        End With

        'Here is where I would like to select ListObjects(table_name).sort..... etc. But it gives me an out of boundry error.
     
    Next rep
    MsgBox ("Sorted")
End Sub
Storax0
Related