Multiple Table Auto-sort on Tab Select

[thenapolitan]

Hey All,

I'm looking for some code to auto-sort 5 separate tables I have in 1 sheet (Table1, Table2, Table3, Table4, Table5) automatically when I select that tab based on a rank I give them (rank being one column in each of the 5 tables).

I'd like to do this so that when my data changes on that sheet, I don't have to go through and manually sort the rank for each of the 5 tables.

Any ideas?

Thanks for any input you could give!

Cheers,

Chris

 

[thenapolitan]

Hey - wondering if anyone could answer this?

Thanks so much!

 

[Peter_SSs]

Is the rank in the same (relative) column in each table?
If so, which column (number)?

 

[thenapolitan]

Is the rank in the same (relative) column in each table?
If so, which column (number)?

Yes, mainly, the rank value (whole number) is listed in column A, but the last table is listed in column B (I could modify my table so that all of the ranks are listed in column A if necessary).

I'd need to sort each table individually.

 

[Peter_SSs]

.. code to auto-sort 5 separate tables I have in 1 sheet (Table1, Table2, Table3, Table4, Table5) automatically when I select that tab ..

.. the rank value (whole number) is listed in column A, but the last table is listed in column B ...

See if this does what you want.
Test in a copy of your workbook.

To implement ..

1. Right click the tables sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window & test by selecting another worksheet then selecting the tables worksheet.

4. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Private Sub Worksheet_Activate()
  Dim tbl As ListObject
  Dim SortCol As Long
  
  Application.ScreenUpdating = False
  For Each tbl In ActiveSheet.ListObjects
    If tbl.Name = "Table5" Then
      SortCol = 2
    Else
      SortCol = 1
    End If
    With tbl.Sort
      .SortFields.Clear
      .SortFields.Add Key:=tbl.DataBodyRange.Columns(SortCol), SortOn:=xlSortOnValues, Order:=xlAscending
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With
  Next tbl
  Application.ScreenUpdating = True
End Sub

 

[thenapolitan]

See if this does what you want.
Test in a copy of your workbook.

To implement ..

1. Right click the tables sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window & test by selecting another worksheet then selecting the tables worksheet.

4. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Private Sub Worksheet_Activate()
  Dim tbl As ListObject
  Dim SortCol As Long
  
  Application.ScreenUpdating = False
  For Each tbl In ActiveSheet.ListObjects
    If tbl.Name = "Table5" Then
      SortCol = 2
    Else
      SortCol = 1
    End If
    With tbl.Sort
      .SortFields.Clear
      .SortFields.Add Key:=tbl.DataBodyRange.Columns(SortCol), SortOn:=xlSortOnValues, Order:=xlAscending
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With
  Next tbl
  Application.ScreenUpdating = True
End Sub

Peter - this worked wonderfully! As it turns out, each table does not start in the "A" column. I did not know this would recognize each object from where it starts (ie. if the object starts in column "C", then column "C" = column "1"). I already have a long list of code making this spreadsheet run, so I am already macro-enabled Since my "rank" is listed in the first column of each object, I was able to remove your If-then-else statement. Works great. Thank you so much for your help!

Cheers!

Chris

 

[Peter_SSs]

Peter - this worked wonderfully! As it turns out, each table does not start in the "A" column. I did not know this would recognize each object from where it starts (ie. if the object starts in column "C", then column "C" = column "1"). I already have a long list of code making this spreadsheet run, so I am already macro-enabled Since my "rank" is listed in the first column of each object, I was able to remove your If-then-else statement. Works great. Thank you so much for your help!

Cheers!

Chris

That's all good news. Thanks for letting us know.
(The 4 numbered points are my copy/paste 'template' points, modified marginally where necessary, for any time I suggest worksheet code, so you always get the macro-enabled comment. )