6
1
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: http://stackoverflow.com/questions/13020878/auto-sort-and-format-and-excel
– lowak – 2014-04-07T10:28:42.107So, 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. – Matthias Wolf – 2014-04-07T10:54:34.487
2The "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 – 2014-04-07T11:02:29.6631Another thing about Worksheets_change is to create good
if
condition, so the macro will not react every time you move to another cell. – lowak – 2014-04-07T11:04:06.347well just played with it and Worksheet_Change seems to be much better than sticking the macro into Worksheet_Calculate, for whatever reason. Runs pretty smoothly now and I guess I can just go with that for the time being. Thanks – Matthias Wolf – 2014-04-07T11:05:20.523
Well, anwsered is pretty simple
Worksheet_Calculate
works when you changeformula
. I suppose your data are not formulas. – lowak – 2014-04-07T11:39:03.900possible duplicate of Is there a dynamic sorting feature in Excel 2007/2010?
– CharlieRB – 2014-04-07T11:53:08.193To add on to the comment by @lowak regarding good
if
statements inworksheet_change
events: I've found the best method is to useif( not( intersect( YOURSHEET.RANGE(YOURRANGE), TARGET) IS NOTHING)) THEN...
as the test. The range held in TARGET is the range that caused theWorksheet_Change
event to trigger. That quick test will check whether the triggering range instersects with the range you want to test and will keep the sheet from resorting every time you sneeze. – JNevill – 2014-04-07T14:44:09.750@lowak, thanks for your offer to get back on this, I find problems with my current macro within Worksheet_Calculate. My settings to Calculate are always set to manual, and I already run another macro that refreshes the whole workbook at specified intervals. My problem is that sometimes when I start the macro that refreshes at specified intervals I get a run time error that originates from the newly inserted macros that refresh the tables. I edited my question to show some code, do you mind taking a look? Thanks – Matthias Wolf – 2014-04-08T10:10:26.107
Where excacly do you get that error? Which macros are "newly inserted"? I think your errors may occur in "DoIt" and "StartTimer". – lowak – 2014-04-08T10:28:46.173
@lowak, the newly inserted is the last code block which runs in
WorkSheet_Calculate()
. The first code block that schedules workbook recalculation sits in "General" and theStartTimer()
method is invoked upon the click of a button in one of the worksheets. I think I may have found the problem: The code that re-sorts the tables is referenced onActiveSheet
which is not correct when being on a different sheet and kicking off the "Workbook re-calculation macro" – Matthias Wolf – 2014-04-08T11:01:36.683Hm... If you want to calculate it only when you are in Worksheet "Strategies" then paste this condition:
If ActiveSheet.Name = "Strategies" Then
and adjust it to your needs :) – lowak – 2014-04-08T11:38:33.3502@Iowak, if you like please write up an answer and I will mark it as sought-after. Thanks again for your valuable help. – Matthias Wolf – 2014-04-29T10:49:22.483