6

Is there a way to re-sort automatically? I have automatically updating cells and depending on incoming values the rankings change. I am looking for a way to have the table resort automatically (similar to conditional formatting) without having to click the re-sort button.

The goal here is to accomplish such purely through a built-in Excel2013 function. I am not looking for a solution that involves additional cells that aide the sort, such as Rank(),...

Edit

I include the code of a macro that refreshes the workbook at set interval and also included code within one worksheet that is supposed to refresh the tables on that one sheet on Worksheet_Calculate. I am getting a runtime error not sure what is wrong?

Public RunWhen As Double
Const frequency = 5
Const cRunWhat = "DoIt"  ' the name of the procedure to run

Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, frequency)
    Application.OnTime RunWhen, cRunWhat, Schedule:=True
End Sub

Sub DoIt()
    Sheets("RAWDATA").Calculate
    ActiveSheet.Calculate
    StartTimer  ' Reschedule the procedure
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime RunWhen, cRunWhat, Schedule:=False
End Sub

and the code that supposedly refreshes the tables

Private Sub Worksheet_Calculate()

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
End With

ActiveSheet.ListObjects("Table2").AutoFilter.ApplyFilter
    With ActiveWorkbook.Worksheets("Strategies").ListObjects("Table2").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

ActiveSheet.ListObjects("Table3").AutoFilter.ApplyFilter
    With ActiveWorkbook.Worksheets("Strategies").ListObjects("Table3").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
End With

End Sub
  • 2
    Well, I think this link should show a way to do it: stackoverflow.com/questions/13020878/… – lowak Apr 7 '14 at 10:28
  • So, I take it there is no function within Excel2013 that activates automated re-sorting of tables? I am surprised about that because conditional formatting adjusts automatically on any changes in source data. So, I find it hard to believe that no sorting functionality exists that re-sorts on changing data. I understand that I can simply record a macro and re-sort manually and plug the code into the WorkshSeet_Change() event method, but its extremely dirty, the slightest change in column renaming breaks the code to just name one issue. – Matt Apr 7 '14 at 10:54
  • 2
    The "screen flickers" can be avoided if you use Application.Screenupdating True / False. First you type False to disable showing any updates made to worksheet and then True to show you what have been done. – lowak Apr 7 '14 at 11:02
  • 1
    Another thing about Worksheets_change is to create good if condition, so the macro will not react every time you move to another cell. – lowak Apr 7 '14 at 11:04
  • 2
    @Iowak, if you like please write up an answer and I will mark it as sought-after. Thanks again for your valuable help. – Matt Apr 29 '14 at 10:49
3

I don't like leaving questions unanswered when they've already been answered it he comments. You can read the history in the comments but here is the final solution:

Private Sub Worksheet_Calculate()
    'If the active sheet is called "Strategies", then this reapplies the filter for two tables and re-sorts them

    Const wsName As String = "Strategies"

    If ActiveSheet.Name = wsName Then

        'Freeze everything and turn off events
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
            .DisplayAlerts = False
        End With

        'Update Table2
        With Worksheets(wsName).ListObjects("Table2")
            .AutoFilter.ApplyFilter
            With .Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End With

        'Update Table3
        With Worksheets(wsName).ListObjects("Table3")
            .AutoFilter.ApplyFilter
            With .Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End With

        'Unfreeze things and turn events back on
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .DisplayAlerts = True
        End With

    End If
End Sub

You could probably even shorten the filtering and sorting to just

    With Worksheets(wsName).ListObjects("Table2")
        .AutoFilter.ApplyFilter
        .Sort.Apply
    End With

This is a community wiki because I did not derive the solution. You can edit it if you want but all I did was transcribe the problem found in the comments and clean up the code a bit.

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.