Automatically sort multiple tables in a sheet upon change

Question

I'm only about three weeks into learning how to use Excel, and I have it so that all of the tables on my worksheet will sort, but not when there's a change, only when I actually visit the worksheet.

So if I input data from another source like a UserForm, it won't sort the tables again until I go back to the worksheet. Is there a way to automatically sort them so the extra visit isn't needed?

This is what I have so far:

Private Sub Worksheet_Activate()
  Dim tbl As ListObject
  Dim SortCol As Long

  Application.ScreenUpdating = False
  For Each tbl In ActiveSheet.ListObjects
    If tbl.Name = "TableSORT2" 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

I tried changing Private Sub Worksheet_Activate() to Private Sub Worksheet_Change() but to no avail, I'm assuming because there's more references or integration needed.

have you tried ActiveSheet.EnableCalculation = True or are you thinking more like or Pivot.RefreshTable? — jamesC
– jamesC, Commented Feb 11, 2015 at 21:22
Can you just write the sort to a macro and then call the macro whenever you add data from the userform? Is everything you are doing contained within one workbook? — D_Zab
– D_Zab, Commented Feb 11, 2015 at 21:31
I'm not sure what either of those mean, but the first one looks like it requires the sheet to be active for it to update instead of just a automated response. I found this, it only updates one row in the sheet for a single table, but it does it without any extra interaction: Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("A:A")) Is Nothing Then Range("A8").Sort Key1:=Range("A9"), _ Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End If End Sub — Tayloryouno
– Tayloryouno, Commented Feb 11, 2015 at 21:33
everything is in one workbook so far, yes. the problem is i don't exactly know how to write said macro yet. — Tayloryouno
– Tayloryouno, Commented Feb 11, 2015 at 21:34

L42 · Accepted Answer · 2015-02-11 22:31:45Z

You can either take on D_Zab's advise or try below:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo halt
    Application.EnableEvents = False
    Dim tbl As ListObject
    For Each tbl In Me.ListObjects
        If Not Intersect(Target, Me.Range(tbl.Name)) Is Nothing Then
            MsgBox "Table Updated"
            SortTables Me 'call the sort table routine
        End If
    Next
moveon:
    Application.EnableEvents = True
    Exit Sub
halt:
    MsgBox Err.Description
    Resume moveon
End Sub

So above detects any changes made in any table in the sheet you put the event.
Now, all you have to do is to create a sub that sorts all the tables and call it.
A sample code (which is actually what you have) is below.

Private Sub SortTables(sh As Worksheet)
    Dim tbl As ListObject
    Dim SortCol As Long

    Application.ScreenUpdating = False
    For Each tbl In sh.ListObjects
        If tbl.Name = "TableSORT2" 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

Is this what you're trying? Btw, for some reason, this also detects changes made from UserForms. Even a simple line like Range("A2").Value = "something" is detected so long as the target range is within the Table Range. Moreover, this also detects the addition of data to tables when it auto-resize. HTH.

Collectives™ on Stack Overflow

Automatically sort multiple tables in a sheet upon change

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related