I wrote a Macro for this excel file and it worked just how I wanted it to. I loaded it to Sharepoint and had a co-worker open it and they got a recovery error and when they hit ok the pop-up showed this "Removed Records: Rich Autofilter from /xl/tables/table1.bin part (Table)" and if you close the error the macro no longer works. I don't know what causes this error.
Sub CopySheet()
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False
ActiveSheet.DisplayPageBreaks = False
Application.Calculation = xlCalculationManual
Dim ws As Worksheet
Dim TableName As String
TableName = ""
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "SORT BY DATE" Then
ws.Delete
Exit For
End If
Next ws
ActiveWorkbook.Sheets.Add
ActiveSheet.Name = "SORT BY DATE"
Sheets("SORT BY DATE").Columns("K").NumberFormat = "dd/mm/yy"
Sheets("SORT BY DATE").Range("G:I").NumberFormat = "dd/mm/yy"
Sheets("SITE LOG").Cells.Copy
Sheets("SORT BY DATE").Paste
Sheets("SORT BY DATE").Range("A4:P3000").ClearContents
Sheets("SORT BY DATE").Range("A4:P2000").Value2 = Sheets("SITE LOG").Range("A4:P2000").Value2
TableName = Sheets("SORT BY DATE").Range("A4").ListObject.Name
Dim tbl As ListObject
Set tbl = Sheets("SORT BY DATE").Range("A4").ListObject
With tbl.sort
.SortFields.Clear
.SortFields.Add Key:=Range("K4"), SortOn:=xlSortOnValues, Order:=xlAscending
.Apply
End With
Sheets("SORT BY DATE").ListObjects(TableName).Range.AutoFilter Field:=12, Criteria1:=Array("NOT STARTED", "ON HOLD", "IN PROCESS"), Operator:=xlFilterValues
Sheets("SORT BY DATE").ListObjects(TableName).Range.AutoFilter Field:=11, Criteria1:=Array("="), Operator:=xlFilterValues, Criteria2:=Array(0, "12/22/2022")
Application.CutCopyMode = False
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub