selecting and sorting tables in varoius sheets

BROM54

New Member
Joined
Apr 6, 2012
Messages
31
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.
Code:
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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

kpark91

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
Hi, the way you can loop through the sheets are by using
Code:
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
Code:
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

New Member
Joined
Apr 6, 2012
Messages
31
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

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
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:
Code:
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

New Member
Joined
Apr 6, 2012
Messages
31
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

New Member
Joined
Apr 6, 2012
Messages
31
Figured it out. Here is the code I used
Thanks for the help


Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,305
Messages
5,600,867
Members
414,410
Latest member
4610

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top