VBA: select table within cell

[MrAxist]

Hey all,

I'm new to VBA and I'm struggling with a problem. I would like to use a (form) command button to run a macro to sort a table by it's date.

However, I've got the following code:

ActiveWorkbook.ActiveSheet.ListObjects("Tabel15").Sort.SortFields. _

Where I would like to change ListObjects("Tabel15") to a variable I can use over multiple sheets.
For example, selecting the table by fixed cells, instead of a table name.

Sub FilterNieuwOud()
'
' FilterNieuwOud Macro
'

'
    Range("B14").Select
    ActiveWorkbook.ActiveSheet.ListObjects("Tabel15").Sort.SortFields. _
        Clear
    ActiveWorkbook.ActiveSheet.ListObjects("Tabel15").Sort.SortFields. _
        Add2 Key:=Range("Tabel15[Datum]"), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.ActiveSheet.ListObjects("Tabel15").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Date rows: B14 to B50, not including headline
Full table: B14 to H50, not including headline

Sorry, it's been a long day and I've been struggling with this for a while. Most likely my code as well as my post is pretty messy, but I would be more than pleased to clearify things up!

 

[jkpieterse]

ActiveCell.ListObject returns the ListObject in which the active cell is.

 

[MrAxist]

But I would like to, instead of using a static name (Table15), use something like row numbers to define the table which to sort.

Resulting in me being able to use the same macro in different sheets.

 

[jkpieterse]

A worksheet can have more than one table, how do you want to choose which table to sort?

 

[jkpieterse]

To sort the table belonging to the active cell, replace this
ActiveWorkbook.ActiveSheet.ListObjects("Tabel15").Sort.SortFields
With
ActiveCell.ListObject.Sort.SortFields

 

[hernantorres23]

Hi, this option make a list with all sheets, then put the list in Data Validation
It is only an idea

Option Explicit

'Hernan Torres, Mikel ERP
'December 9, 2019

Sub order_by_selection()
Dim start_b As Range
Dim sheetActive, activeRange As Range
Dim i As Integer
Dim b() As Variant

On Error GoTo ctrl_error
Sheets("Master").Activate
Run ("hojas_en_libro")
Application.ScreenUpdating = False
    Range("A3").Select
    Set start_b = Sheets("Master").Range("A" & ActiveCell.Row)
    b = Range(start_b, Cells(Rows.Count, "A").End(xlUp))
    For i = 1 To UBound(b, 1)
    
    If Not IsEmpty(b(i, 1)) Then
    Set sheetActive = Sheets(b(i, 1)).Range("B13").CurrentRegion
    Sheets(b(i, 1)).Sort.SortFields.Clear
    Sheets(b(i, 1)).Sort.SortFields.Add Key:=Range("B14:B50") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Sheets(b(i, 1)).Sort
        .SetRange Range(sheetActive.Address)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    

    End If
    Next
Application.ScreenUpdating = True

salir:
GoTo finalizar

ctrl_error:
'Debug.Print Err.Number & " " & Err.Description
Select Case Err.Number
Case 0
Resume salir
Case Else
MsgBox "Error N: " & Err.Number & " - " & Err.Description
Exit Sub
End Select

finalizar:
MsgBox UBound(b, 1) & " sheets were ordered", vbInformation, "Mikel ERP by htorres"

End Sub


Sub hojas_en_libro()
Dim i, j As Integer
Dim nombre As String
Dim listSheets As Variant
Dim a As Range

Range("AZ:AZ").ClearContents
j = ThisWorkbook.Sheets.Count
Set a = Range("AZ2", Cells(j, "AZ"))
For i = 1 To j
nombre = ThisWorkbook.Sheets.Item(i).Name
Cells(i, 52) = nombre
Next
ActiveWorkbook.Names.Add "listSheets", , , , , , , , , a
End Sub

and here an Example