Multiple Table Auto-sort on Tab Select

[DaveGooch]

Hi, Appreciate that this is an old post - however this code has really worked well for me. I have a question though. Can it be modified to sort column 3 then column 2 on every table in the sheet?

Thanks,

Dave

 

[Peter_SSs]

Welcome to the MrExcel board!

Best to start your own thread and link to an old one. I will do that for you in this case.
You'll likely get more people looking at a new thread.

See if this does what you want.

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

 

[DaveGooch]

You're a legend!! Thank you very much!

I adjusted it to do large to small in column 3 and small to large in column 2. Odd that it seemed to do it out of order? if I put it as descending column 3 first it didn't work.

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

 

[DaveGooch]

Hi, when I save this file I often get an error that says I cannot save the file and need to save a new version - seems to be linked to an issue with this van code - can you see anything wrong with it? It would it be better if I assigned it to a button? (Any updates needed to make that work?). Thanks, Dave

 

[Fluff]

What is the file extension of the file?

 

[DaveGooch]

Hi, when I save this file I often get an error that says I cannot save the file and need to save a new version - seems to be linked to an issue with this van code - can you see anything wrong with it? It would it be better if I assigned it to a button? (Any updates needed to make that work?). Thanks, Dave

What is the file extension of the file?

.xlsm

 

[Fluff]

I can't see anything in that code which would cause a problem for an xlsm file.
What is the exact error message you get?