Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-20-2017, 11:43 PM
trevorc trevorc is offline sort dynamic table name with vba Windows 7 32bit sort dynamic table name with vba Office 2013
Competent Performer
sort dynamic table name with vba
 
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

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

Reply With Quote
  #2  
Old 01-21-2017, 12:58 AM
trevorc trevorc is offline sort dynamic table name with vba Windows 7 32bit sort dynamic table name with vba Office 2013
Competent Performer
sort dynamic table name with vba
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default 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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
sort dynamic table name with vba 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
sort dynamic table name with vba How to sort table having three columns? Bahir Barak Word 2 01-20-2011 01:52 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:27 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft