• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sorting Multiple Tables in Worksheet by VBA

D

Dokat

Member
Hi,

I have multiple tables in a workshseet and i want each table to automatically sort based on latest 52 week sales numbers.

First table is located between F16:AO26 sort by desecnding order based on column AI
Second table is located F30:AO57 sort by desecnding order based on column AI
Third table is located F62:AO84 sort by desecnding order based on column AI

I use below code to sort one table at a time. How can i modify the code and so that i can automatically sort all tables at once? Thank you

Code: 
PrivateSub Worksheet_Change(ByVal Target As Range)'turn off updates to speed up code executionWith Application
.ScreenUpdating =False.EnableEvents =False.Calculation = xlCalculationManual.DisplayAlerts =FalseEndWith

IfNot Intersect(Target, Columns(1))IsNothingThen

With ActiveSheet.Sort.SetRange Range("f16:ao26"& Cells(Rows.Count,1).End(xlUp).Row).Header = xlYes.MatchCase =False.Orientation = xlTopToBottom.SortMethod = xlPinYin.ApplyEndWith

EndIf

With Application.ScreenUpdating =True.EnableEvents =True.Calculation = xlCalculationAutomatic.DisplayAlerts =TrueEndWith

EndSub
 
Hi ,

In the absence of your workbook , I cannot test this ; try the following code :
Code: 
Private Sub SortData()
'turn off updates to speed up code execution
            With Application
                .ScreenUpdating = False
                .EnableEvents = False
                .Calculation = xlCalculationManual
                .DisplayAlerts = FalseEndWith
            End With

            With ActiveSheet.Sort
                .SortFields.Clear
                .SortFields.Add Key:=Range("AI17:AI26"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
                .SetRange Range("f16:ao26")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With

            With ActiveSheet.Sort
                .SortFields.Clear
                .SortFields.Add Key:=Range("AI31:AI57"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
                .SetRange Range("f30:ao57")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With

            With ActiveSheet.Sort
                .SortFields.Clear
                .SortFields.Add Key:=Range("AI63:AI84"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
                .SetRange Range("f62:ao84")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With

            With Application
                .ScreenUpdating = True
                .EnableEvents = True
                .Calculation = xlCalculationAutomatic
                .DisplayAlerts = True
            End With
End Sub
You will have to run this procedure SortData manually ; I don't see any reason why all this code should be part of a Worksheet_Change procedure.

Prior to running this macro , ensure that your worksheet tab named HDD is the active sheet.

Narayan
 
You must log in or register to reply here.
Back
Top