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:
Here is the "FilterDate Code"
Here is the "Open Workbook" code:
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:
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"
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:
Code:
Private Sub Workbook_Open()
FilterDate
End Sub