Microsoft Office Forums - View Single Post - [Solved] sort dynamic table name with vba

trevorc · 01-20-2017, 11:43 PM

Hi All,
I am trying to sort a table using vba, my problem is that the table name will change as new tables are created on the sheet. The only place that seems to be an issue is the text highlighted in red in the code below. I can use the reference - "Table" & foundNum for other parts of my code and it works ok untill I try it as shown in place of Table1. I'm hoping that it's just a syntax issue that sombody can help me with. I have searched the web and found some code but nothing so far that fixes the problem I have. It returns the error - Method 'range' of object '_global' failed

Code:

                ActiveSheet.ListObjects.Add(xlSrcRange, Range(tbl_start & ":$AA$" & row18), , xlYes).Name = "Table" & foundNum ' Adds table with dynamic name eg could be table1 to table25 .....
                ActiveWorkbook.Worksheets("Instructions").ListObjects("Table" & foundNum).Sort.SortFields.Clear ' seems to work during debugging
                ActiveWorkbook.Worksheets("Instructions").ListObjects("Table" & foundNum).Sort.SortFields _
                .Add Key:=Range("Table1[[#All],[Sheet & Location]]"),SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                With ActiveWorkbook.Worksheets("Instructions").ListObjects("Table" & foundNum).Sort
                    .Header = xlYes
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                End With