Hi guys. Long time lurker, first time poster!
I've searched long and hard for a solution to this, but I'm just a derp when it comes to macros so I need some help.
I have a spreadsheet to keep track of daily jobs in progress. It consists of 6 tables (5 weekdays + 1 miscellaneous), each table lists customers, job descriptions, and details. The first column will have "D" for done or "I" for in progress. The macro is set up to automatically re-sort (Z to A) on cell changes so that anything marked "D" moves to the bottom. This all works great.
However, any time I clear cells or delete a row, Excel locks up. I'm sure it has something to do with the "Clear" commands listed below, but I removed those and it still screws up. Any ideas?
Thanks!
Andrea
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
ActiveWorkbook.Worksheets("Tables").ListObjects("Table1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Tables").ListObjects("Table1").Sort.SortFields.Add _
Key:=Range("Table1[[#All],[Fab/Done?]]"), SortOn:=xlSortOnValues, Order:= _
xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Tables").ListObjects("Table1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("Tables").ListObjects("Table14").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("Tables").ListObjects("Table14").Sort.SortFields.Add _
Key:=Range("Table14[[#All],[Fab/Done?]]"), SortOn:=xlSortOnValues, Order _
:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Tables").ListObjects("Table14").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("Tables").ListObjects("Table145").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("Tables").ListObjects("Table145").Sort.SortFields. _
Add Key:=Range("Table145[[#All],[Fab/Done?]]"), SortOn:=xlSortOnValues, _
Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Tables").ListObjects("Table145").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("Tables").ListObjects("Table146").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("Tables").ListObjects("Table146").Sort.SortFields. _
Add Key:=Range("Table146[[#All],[Fab/Done?]]"), SortOn:=xlSortOnValues, _
Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Tables").ListObjects("Table146").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("Tables").ListObjects("Table147").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("Tables").ListObjects("Table147").Sort.SortFields. _
Add Key:=Range("Table147[[#All],[Fab/Done?]]"), SortOn:=xlSortOnValues, _
Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Tables").ListObjects("Table147").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("Tables").ListObjects("Table148").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("Tables").ListObjects("Table148").Sort.SortFields. _
Add Key:=Range("Table148[[#All],[Fab/Done?]]"), SortOn:=xlSortOnValues, _
Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Tables").ListObjects("Table148").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Bookmarks