#1
|
|||
|
|||
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 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 |
#2
|
|||
|
|||
neer mind I found the solution
So it was syntax, just had to figure out how to get it in there... See below if you also need this.
Code:
tbl_name = "Table" & foundNum & "[[#All],[Sheet & Location]]" tbl1_name = "Table" & foundNum ActiveSheet.ListObjects.Add(xlSrcRange, Range(tbl_start & ":$AA$" & row18), , xlYes).Name = "Table" & foundNum ActiveWorkbook.Worksheets("Instructions").ListObjects(tbl1_name).Sort.SortFields.Clear ActiveWorkbook.Worksheets("Instructions").ListObjects(tbl1_name).Sort.SortFields _ .Add Key:=Range(tbl_name), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Instructions").ListObjects(tbl1_name).Sort .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Dynamic table numbers | srinidhi.mv88 | Word VBA | 5 | 06-22-2015 06:35 AM |
Dynamic Mail Merged Table | hanskimber | Mail Merge | 2 | 04-24-2015 11:21 PM |
VBA sort table code | mikec | Excel Programming | 8 | 10-01-2013 04:37 PM |
Sort table using macro | saslotteroy | Word VBA | 2 | 09-15-2011 02:41 PM |
How to sort table having three columns? | Bahir Barak | Word | 2 | 01-20-2011 01:52 PM |