View Single Post
Old 01-20-2017, 11:43 PM
trevorc trevorc is offline Windows 7 32bit Office 2013
Competent Performer
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default sort dynamic table name with vba

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

                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
                End With
Reply With Quote