I'm working on a code to filter my table, but I'm struggling to set the 'Key:=Range("")' , so far I've tested a code without setting Dims and it works, but I want a more pratical approach, so the code will work in all worksheets (active worksheet) in my workbook. Error im getting: Method range of object _'Global' failed. Error 1004

So on resume, im new on VBA and dont now how to set MyTable(Tbl) on the 'Key:=Range("Tbl[[#All],[Column1]]")

Sub MAKE_FILTER()
   
Dim ws As Worksheet
Dim wb As Workbook
Set wb = ThisWorkbook
Set ws = ActiveSheet
Dim Tbl As Object
Set Tbl = ws.ListObjects(1)


    Tbl.Range.AutoFilter Field:=1, Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor
    Tbl.Sort.SortFields.Clear
    Tbl.Sort.SortFields.Add2 Key:=Range("Tbl[[#All],[DANFE]]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Tbl.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Tbl.Sort.SortFields.Clear
    Tbl.Sort.SortFields.Add2 Key:=Range("Tbl[[#All],[Nº NF-e]]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Tbl.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Without DIMs:

Sub Macro1() 'without DIMs

    ActiveSheet.ListObjects("Tabela14212255").Range.AutoFilter Field:=1, _
        Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor 'ok
    ActiveWorkbook.Worksheets("NOVEMBRO 2022").ListObjects("Tabela14212255").Sort. _
        SortFields.Clear 'ok
    ActiveWorkbook.Worksheets("NOVEMBRO 2022").ListObjects("Tabela14212255").Sort. _
        SortFields.Add2 Key:=Range("Tabela14212255[[#All],[DANFE]]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("NOVEMBRO 2022").ListObjects("Tabela14212255"). _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("NOVEMBRO 2022").ListObjects("Tabela14212255").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("NOVEMBRO 2022").ListObjects("Tabela14212255").Sort. _
        SortFields.Add2 Key:=Range("Tabela14212255[[#All],[Nº NF-e]]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("NOVEMBRO 2022").ListObjects("Tabela14212255"). _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

CodePudding user response：

You can use:

Key:=tbl.Listcolumns("Nº NF-e").Range

to refer to the column by name.

CodePudding user response：

Filter and Sort Tables (ListObjects)

Sub ApplyFilter()
   
    Dim Headers() As Variant: Headers = Array("DANFE", "No NF-e")
    
    ' Reference 'ActiveSheet' and 'ThisWorkbook'.
    
    If ActiveSheet Is Nothing Then Exit Sub ' no visible workbooks open
    If Not TypeOf ActiveSheet Is Worksheet Then Exit Sub ' not a worksheet
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    If Not ws.Parent Is wb Then
        MsgBox "This only works for worksheets in the workbook containing " _
            & "this code named '" & wb.Name & "' and located in '" _
            & wb.Path & "'.", vbCritical
        Exit Sub
    End If
    
    ' Reference the table.
    
    Dim lo As ListObject
    On Error Resume Next
        Set lo = ws.ListObjects(1) ' the first table
    On Error GoTo 0
    
    If lo Is Nothing Then
        MsgBox "The worksheet '" & ws.Name & "' doesn't contain any tables.", _
            vbExclamation
        Exit Sub
    End If
    
    ' Filter and sort.
    
    With lo
        
        If .ShowAutoFilter Then ' autofilter arrows are turned on
            ' Clear all filters.
            If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
        End If
        
        .Range.AutoFilter 1, RGB(255, 255, 0), xlFilterCellColor
        
        Dim lc As ListColumn, n As Long
        
        For n = LBound(Headers) To UBound(Headers)
            ' Reference the column.
            On Error Resume Next
                Set lc = .ListColumns(Headers(n))
            On Error GoTo 0
            If lc Is Nothing Then
                MsgBox "No column named '" & Headers(n) & "' in the table " _
                    & "named '" & .Name & "' of worksheet '" _
                    & .Parent.Name & "'.", vbCritical
                Exit Sub
            End If
            ' Sort by the column.
            With .Sort
                With .SortFields
                    .Clear
                    .Add2 lc.Range, xlSortOnValues, xlAscending, , xlSortNormal
                End With
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
            Set lc = Nothing ' reset for the next iteration
        Next n
    
    End With

End Sub