Solved ExcelVBA Q re: Sorting Multiple Worksheets with Table Format

Microsoft Excel 2010 - complete product...
May 14, 2018 at 18:49:35
Specs: Windows 10 Enterprise
I am trying to use the below code (example) within a larger macro, so that any of the worksheets
that are in a table format, will be alpha sorted by a specified column (the column name is consistently included in all worksheets with the table formatting, but the location of the column varies by worksheet.)
The problem with the below code is that it refers to the listobject as ‘Table1’ but if there are 5 worksheets with tables, they will be named: “Table1”, “Table1_1”, “Table1_2”, “Table1_3” and “Table1_4” … so the sorting is done on the first table since it is the only one with the exact ‘Table1’ name. The number of worksheets within a workbook that will contain tables varies, so I cannot use a fixed number of worksheet names for this sort procedure.

Can anyone help?

Sub Sort_theTableFormat_Sheets()

Dim ws as worksheet
Dim wb as workbook

 For Each ws In wb.Sheets    

                 If ActiveSheet.ListObjects.Count > 0 Then ActiveSheet.ListObjects(1).ShowAutoFilter = False
                             'Sort Worksheet Rows alphabetically by the column named “Oranges”

                              ws.ListObjects("Table1").Sort.SortFields.Add _
                               Key:=Range("Table1[Oranges]"), SortOn:=xlSortOnValues, Order:=xlAscending _
                               , DataOption:=xlSortNormal

                                     With ActiveSheet.ListObjects("Table1").Sort
                                         .Header = xlYes
                                         .MatchCase = False
                                         .Orientation = xlTopToBottom
                                         .SortMethod = xlPinYin
                                     End With

                             ActiveSheet.ListObjects(1).ShowAutoFilter = True
                  Else   'do nothing
                  End If                  

End Sub

See More: ExcelVBA Q re: Sorting Multiple Worksheets with Table Format

May 14, 2018 at 20:42:22
✔ Best Answer

Instead of this:

For Each ws In wb.Sheets

Try this:

  For sht = 1 To Worksheets.Count
     If sht = 1 Then
         t_name = "Table1"
     Else: t_name = "Table1_" & shts - 1
     End If

'...the rest of your code...


End Sub

Then, instead of hard-coding the name "Table1" throughout the rest of your code, use the variable t_name. You'll also have to forego the use of ws and refer to your sheets as Sheets(sht).

message edited by DerbyDad03

Report •

May 16, 2018 at 19:22:33
Thank you for your suggestion. Unfortunately, I could not get the code to work correctly using the alternate approach so I finally ended up converting the table to a range, sorting and then converting back to a table.

message edited by User444

Report •

May 17, 2018 at 07:42:53
re: "I could not get the code to work correctly"

Perhaps if you told me what did not work, I could help you fix it.

I can't test the code because I do not have your book to run it against, so I would need to rely on you telling me what errors you are getting or what happened when you tried it. It might be nothing more than a syntax error or a misunderstanding of what I am suggesting.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Report •
Related Solutions

Ask Question