VBA: Use Variable with Sort Range

[zero269]

Hello,

I'm trying to convert one of my recorded macros to use dynamic values instead of the Table or column header names.
So far, I've got everything working up to this point. This is the one line where I can't figure out how to use variables with:

SortFields.Add Key:=Range("tblBooks6[[#Headers],[#Data],[Book Level]]")

Here's what I'm currently working with:

'Filter Book Level Range
Sub testSort_v1()

'Get Sheet Name
Dim SheetName As String
SheetName = ActiveSheet.Name

'Get Table Name
Dim TblName As String
TblName = ActiveCell.ListObject.Name

'Get "Book Level" column number
Dim ColNum As Integer
ColNum = ActiveSheet.ListObjects(TblName).ListColumns("Book Level").Index

'Sort Book Level Ascending
    ActiveWorkbook.Worksheets(SheetName).ListObjects(TblName).Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets(SheetName).ListObjects(TblName).Sort. _
        SortFields.Add Key:=Range("tblBooks6[[#Headers],[#Data],[Book Level]]")
    With ActiveWorkbook.Worksheets(SheetName).ListObjects(TblName).Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

I'm using a filter as well - not included here - where I get the Column number to filter by. Perhaps one way would be to use the column number with sort...? Online resources point to using Range, but I never stumbled on anyone using the column number to identify the column to sort.

Any insight would be greatly appreciated.

Thank you,

A sample data visual for reference if needed...

 

[RoryA]

You seem to be going a very roundabout way for this. Try something like:

Sub testSort_v1()

'Get Table
Dim lo as listobject
set lo = ActiveCell.ListObject

'Sort Book Level Ascending
    with lo.Sort
        with .SortFields
            .Clear
            .Add Key:=lo.Listcolumns("Book Level").Range
        end with
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

 

[zero269]

You seem to be going a very roundabout way for this. Try something like:

True that. VBA confuses the hell out of me, so I have to take baby steps on trying to make sense of those recorded macros in hopes to make them more dynamic in the future. Unfortunately, I haven't seen a lot of success outside of this forum.

Thanks for the VBA code you provided...

I ran it in my sample workbook, but it's throwing the following run-time error:

I noticed in the Locals window that the lo variable was showing a value of Nothing...

So I thought I could add .Name to get the Table Name, but that just threw up another error:

I assume the Table Object is not being identified...?

Thanks,

 

[MARK858]

Did you have a cell within the table selected when you ran the code?

 

[zero269]

Did you have a cell within the table selected when you ran the code?

An excellent catch, Mark! A total brain fart on my end...

Interestingly enough, just finished studying up on working with Tables using VBA. One thing I picked up was selecting Tables, so I went ahead and added the following at the beginning of the code to select the first table in the active sheet in case I do that again.

ActiveSheet.ListObjects(1).HeaderRowRange(1).Select

Much better now. Thanks...

 

[zero269]

You seem to be going a very roundabout way for this. Try something like:

Hi Rory,
Disregard my first reply regarding the run-time error. It was my mistake that Mark was able to catch.
Everything works perfectly!
Thanks again for your help on this.