selecting and sorting tables in varoius sheets

[BROM54]

Hello,
I need to writ a macro that will do a loop that selects each sheet, then selects the table in that sheet, then sorts the table. So far I can select the table from one sheet. I know what the names of the other tables will be though. Here is my code so far.

Sub SortTable()
    Range("Table8").Select
    ActiveWorkbook.Worksheets("JUNE").ListObjects("Table8").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("JUNE").ListObjects("Table8").Sort.SortFields.Add _
        Key:=Range("Table8[Days Out]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("JUNE").ListObjects("Table8").Sort.SortFields.Add _
        Key:=Range("Table8[DATE OUT UNSIGNED]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("JUNE").ListObjects("Table8").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
  End Sub

 

[kpark91]

Hi, the way you can loop through the sheets are by using

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    'Looping through each worksheet
Next ws

and instead of using ActiveWorksheet or Select method, it'll be much better off by using explicit statements.
So the above code you have posted would be

Sub SortTable()
    For Each ws In ThisWorkbook.Worksheets
    ws.ListObjects("Table8").Sort.SortFields.Clear
    ws.ListObjects("Table8").Sort.SortFields.Add _
     Key:=Range("Table8[Days Out]"), SortOn:=xlSortOnValues, Order:= _         
             xlAscending, DataOption:=xlSortNormal     
    ws.ListObjects("Table8").Sort.SortFields.Add _ 
     Key:=Range("Table8[DATE OUT UNSIGNED]"), SortOn:=xlSortOnValues, Order:= _         
             xlAscending, DataOption:=xlSortNormal 

    With ActiveWorkbook.Worksheets("JUNE").ListObjects("Table8").Sort 
          .Header = xlYes         
          .MatchCase = False         
          .Orientation = xlTopToBottom         
          .SortMethod = xlPinYin 
          .Apply     
    End With
    Next ws
End Sub

The code posted assumes the names of the Tables in all worksheets are consistent as Table 8.

 

[BROM54]

The table names are actually different for each one. As far as I can tell, you cannot give tables the same name if they are in the same workbook. Looping the worksheets isn't my problem, it is searching each worksheet for a table with a different name. The tables are named the same as the sheets, but I do not know how many sheets there will be at any given time.

 

[kpark91]

The tables are named the same as the sheets, but I do not know how many sheets there will be at any given time.

In that case, try:

Sub SortTable()
    For Each ws In ThisWorkbook.Worksheets
    ws.ListObjects(ws.Name).Sort.SortFields.Clear
    ws.ListObjects(ws.Name).Sort.SortFields.Add _
     Key:=Range(ws.Name & "[Days Out]"), SortOn:=xlSortOnValues, Order:= _         
             xlAscending, DataOption:=xlSortNormal     
    ws.ListObjects(ws.Name).Sort.SortFields.Add _ 
     Key:=Range(ws.Name & "[DATE OUT UNSIGNED]"), SortOn:=xlSortOnValues, Order:= _         
             xlAscending, DataOption:=xlSortNormal 

    With ws.ListObjects(ws.Name).Sort 
          .Header = xlYes         
          .MatchCase = False         
          .Orientation = xlTopToBottom         
          .SortMethod = xlPinYin 
          .Apply     
    End With
    Next ws
End Sub

As an alternative, if you have only one table for each worksheet, you can try selecting the table that way.
I have no idea how your worksheets are laid out so I doubt the above code will work but there are bound to be hits & misses

 

[BROM54]

Basically each sheet is a list of delivery tickets for the month. The table lists when they were sent out, brought back, and how many days they have been out if they haven't returned yet. Each sheet is basically only the table on the sheet with some minor calculations in the table.

I think it would work if I could get it to activate each sheet 1 at a time. Then I could use the activesheet property to dim the name of the range. Is there a way I can get it to go sheet by sheet and activate them?

 

[BROM54]

Figured it out. Here is the code I used
Thanks for the help

Private Sub Workbook_Open()
Dim ws As Worksheet
Dim wsname As String
Dim i As Integer


     
    For i = 1 To Worksheets.Count
        If Sheets(i).Visible Then
            Sheets(i).Activate
            Exit For
            End If
        Next i

For Each ws In ThisWorkbook.Worksheets
wsname = ActiveSheet.name
    ActiveWorkbook.Worksheets(wsname).ListObjects(wsname).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(wsname).ListObjects(wsname).Sort.SortFields.Add Key:= _
        Range(wsname & "[Days Out]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets(wsname).ListObjects(wsname).Sort.SortFields.Add Key:= _
        Range(wsname & "[DATE OUT UNSIGNED]"), SortOn:=xlSortOnValues, Order:=xlAscending _
        , DataOption:=xlSortNormal
    
    With ActiveWorkbook.Worksheets(wsname).ListObjects(wsname).Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   Dim idx As Integer

idx = ActiveSheet.Index
If idx = Sheets.Count Then
Exit Sub
ElseIf Sheets(idx + 1).Visible = False Then
Do Until Sheets(idx + 1).Visible = True
idx = idx + 1
Loop
Sheets(idx + 1).Activate
Else
Sheets(idx + 1).Activate
End If
    Next ws
End Sub