VBA Code To Sort and Autofilter Specific Date Column with Header

[chelsea199999]

I have to work with an Excel File every week that contains dates of signed contracts. The file varies in size depending on the week. The dates are always listed in Column K of the spreadsheet in randomized order.

I would like to have a macro that automates the clean-up. Mainly, one that 1) autofilters out dates before a given date, and 2) sorts the results (sans header) in chronological order (oldest to newest).

I have tried to code it myself with the following VBA:

Sub Sortdate()
    Dim Ac As Integer
    Dim Lastr As Long
    Dim Sh As Worksheet
    Dim sDate As String
    
Set Sh = ActiveSheet
    Ac = ActiveCell.Column
    Lastr = Sh.Cells(Rows.Count, Ac).End(xlUp).Rows
    Date = InputBox("Please Enter Deal Expration Date.")

Selection.AutoFilter
Range(Cells(2, Ac), Cells(Lastr, Ac)).AutoFilter Field:=11, Criteria1:= _">" & CDate(sDate)

With Sh
Range(Cells(2, Ac), Cells(Lastr, Ac)).Sort Key1:=Range(Cells(2, Ac), Cells(Lastr, Ac)), Order1:=xlAscending, DataOption1:=xlSortNormal, MatchCase:=False, SortMethod:=xlPinYin, _Header:=xlYes, Orientation:=xlTopToBottom.Sort.Apply

End With
End Sub

However, whenever I run the code, an error message pops up. I am very new to VBA coding, may anyone please offer some guidance?

 

[Micron]

Posting to help you help others to help you. Always post the error number and the error message. It helps to focus replies. Most often, the issue isn't obvious just by reading the code.

Here's a guess. You don't have Option Explicit at the top of each module. That allows you to use the word Date as a variable, which you should not. Also allows you to attempt to pass a string to the Date function, because the Inputbox function returns a string.

 

[chelsea199999]

Hi Micron! Thank you so much for your suggestion. The error code that is popping up is the following:

Run time error 1004
Autofilter method of range class failed

 

[Micron]

Maybe it's the 1 in Criteria1? Or the underscore in the same line. I guess forum vba tags don't vet the syntax of code because when I pasted your code, a couple of lines are red, and that's bad. Not so for you? Perhaps

Range(Cells(2, Ac), Cells(Lastr, Ac)).AutoFilter Field:=11, criteria:=">" & CDate(sDate)
or if not, perhaps
Range(Cells(2, Ac), Cells(Lastr, Ac)).AutoFilter Field:=11, criteria:=">#" & CDate(sDate) & "#"

If one of those works, then there's the issue with the Range line. Perhaps the underscore in _Header:=xlYes