Sep 06 2022 09:13 AM
Hello-
i have a spreadsheet I use to manage my projects, but installation dates constantly change. I have the rows organized based on date of installation. Is there a way I can put a code in or feature so that when I change the date on that row it moves it in order?
Sep 06 2022 12:17 PM
I wouldn't recommend sorting the data as you enter them - it is very disconcerting if a row moves out of view the moment you enter a date.
Instead, I'd use code to sort the data when you open the workbook and when you activate the sheet (from another sheet).
Let's say you want to sort a sheet named Data Sheet on column D.
1) Right-click the sheet tab of Data Sheet.
Select View Code from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Range("D1").CurrentRegion.Sort Key1:=Range("D1"), Header:=xlYes
Application.ScreenUpdating = True
End Sub
2) Double-click ThisWorkbook in the project explorer pane on the left (in the Visual Basic Editor).
Copy the following code into the ThisWorkbook module:
Private Sub Workbook_Open()
Application.ScreenUpdating = False
With Worksheets("Data Sheet")
.Range("D1").CurrentRegion.Sort Key1:=.Range("D1"), Header:=xlYes
End With
Application.ScreenUpdating = True
End Sub
3) Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm)
Make sure that you allow macros when you open it.