VBA Autofilter Not Working with Date

[JNM]

Good afternoon,

I am getting error 1004 "AutoFilter method of Range class failed" when opening a workbook. I would like the workbook to autofilter the Start Date to those dates from the past two weeks upon opening.

I would appreciate any help or suggestions...


The workbook has one worksheet in it that has "on change" code that autopopulates the date and time fields. Here is that code:

Private Sub Worksheet_Change(ByVal Target As Range)

    
    Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    With Sheet1
        With .Range("A6 :A" & LastRow)
            .AutoFilter
             .AutoFilter Field:=1, Criteria1:=Range("A1").Value
        End With
    End With
  
    If Not Application.Intersect(Target, Columns("D:D")) Is Nothing Then
        Target.Offset(0, 1).Value = Format(Now, "mm/dd/yy")
        Target.Offset(0, 2).Value = Format(Now, "hh:mm AM/PM")
    End If
    
    If Not Application.Intersect(Target, Columns("G:G")) Is Nothing Then
        Target.Offset(0, 1).Value = Format(Now, "mm/dd/yy")
        Target.Offset(0, 2).Value = Format(Now, "hh:mm AM/PM")
    End If
  
End Sub

Here is the "FilterDate Code"

Sub FilterDate()
Dim Date1 As Date
Date1 = Date - 14
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
    
With Sheet1
           .AutoFilterMode = False
        With .Range("A6 :A" & LastRow)
            .AutoFilter
                .AutoFilter Field:=5, Criteria1:=">=" & Date1, visibledropdown:=False
            End With
End With
    
End Sub

Here is the "Open Workbook" code:

Private Sub Workbook_Open()
FilterDate
End Sub

 

[dmt32]

Hi,
see if this update to your FilterDate code helps:

Sub FilterDate()
    Dim Date1 As Date
    Dim lDate As Long, LastRow As Long


    Date1 = Date - 14


    lDate = DateSerial(Year(Date1), Month(Date1), Day(Date1))


    With Sheet1
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .AutoFilterMode = False
        With .Range("A6 :A" & LastRow)
            .AutoFilter
            .AutoFilter Field:=1, Criteria1:=">=" & lDate, visibledropdown:=False
        End With
    End With


End Sub

Dave

 

[JNM]

Dave,

Thank you for the revised code. It worked perfectly!!
JNM