Sort By date with out selecting a cell or row

[tony.reynolds]

Im trying to sort my workbook by date and the code below works ok but it seems to have issues in some circumstances due to othe code conflickting

is there a better option to sort the sheet by the date values in column A:A

I also wanted it the remove or not show the sort button so the user can fidddle with the sorting

Sub SortByDate()
Sheets("Master Accounts").Activate
    Worksheets("Master Accounts").Unprotect (3221)
    Set ReturnCell = ActiveCell
    Application.ScreenUpdating = False
    
    Rows("4:4").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Master Accounts").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Master Accounts").AutoFilter.Sort.SortFields.Add Key _
        :=Range("A4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Master Accounts").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.AutoFilter
    Worksheets("Master Accounts").Protect (3221)
    
    ReturnCell.Activate
    Application.ScreenUpdating = True
    
End Sub

Ny help would be really appreciated

 

[pbornemeier]

Using Selection.AutoFilter toggles the autofilter. If it was on, it just got turned off.
Try this instead:

ActiveSheet.AutoFilterMode = False 'Ensures autofilter mode is off
Rows(4).AutoFilter 'Turns on autofilter for row 4

Excel 2007 and later can prevent sorting of a protected sheet.

What kind of conflicts are you talking about?

 

[tony.reynolds]

hey that explains why when i run this macro it sometimes ended with an error as another macro had Selection.AutoFilter in it turning it on before this ran so this macro would have turned the filter of then tried to filter!

thanks a stack pbornemeier