VBA Sort Data Descending Order in two tables

[hrzagi]

I have two tables with different data that should be arranged by the height of the value in column C. Using the VBA code below I manage to sort the data in Table 1 or the data in cells c4 to c16. However, below it is another table with data from c19 to c26 and these data are not sorted. My question is how to sort them independently of the first table (meaning in Table 2 the highest value may be 30 while in Table 1 it will be among the lower values). And to note, sorting should take place automatically as soon as the value is entered. Thanx :)


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("C4").Sort Key1:=Range("C5"), Order1:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

[georgiboy]

You can try the below, change the number on the 'Column1' bit to suit the column number in the table you are sorting by:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tbl As String

tbl = Target.ListObject.Name

With Me.ListObjects(tbl)
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Me.Range(tbl & "[[#All],[Column1]]"), SortOn:=xlSortOnValues, Order:=xlDescending
With .Sort
.Header = xlYes
.Apply
End With
End With
End Sub

Hope this helps

[arnelgp]

Private Sub Worksheet_Change(ByVal Target As Range)
Call sort_my_tables


End Sub




on a Module:


Public Sub sort_my_tables()
Const this_worksheet As String = "sheet1" 'put the sheet name
Const table_1 As String = "table1" 'put the name of table1
Const column_1 As String = "[last name]" 'put the column name (enclosed in []) to sort
Const order_1 As Integer = 2 'descending
Const table_2 As String = "table2" 'put the name of second table
Const column_2 As String = "[a]" 'put the column name (enclosed in []) to sort
Const order_2 As Integer = 1 'ascending 'ascending

Dim ws As Worksheet

Set ws = Sheets(this_worksheet)
Dim tbl As ListObject
Set tbl = ws.ListObjects(table_1)
Dim sortcolumn As Range
Set sortcolumn = Range(table_1 & column_1)
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=order_1
.Header = xlYes
.Apply
End With


Set tbl = ws.ListObjects(table_2)
Set sortcolumn = Range(table_2 & column_2)
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=order_2
.Header = xlYes
.Apply
End With


Set tbl = Nothing
Set ws = Nothing
Set sortcolumn = Nothing

End Sub

[hrzagi]

Works like a charm, thanks alot :thumb

[snb]

This code is sufficient:


Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Target.ListObject.Range.Sort Target.ListObject.Range.Cells(1), 2, , , , , , 1
End Sub