Excel: cannot get it to auto filter


i cant seem to find whats my mistake from the code. when i do manually filter i can get the table to filter the data base on my criteria. howver when i run my vba code, it cannot filter the data with the criteria.

Sub CopyFilter()'clear the contents
    Sheet12.Range("B4:P10000").ClearContents
'copy and paste the range
    Sheet7.Range("Database").SpecialCells(xlCellTypeVisible).Copy _
    Destination:=Sheet12.Range("B4")
End Sub
Sub Between2Dates()'declare the variables
Dim DateBegin As Date
Dim DateEnd As Date
Dim Rng As Range
'set error handler
'On Error GoTo errHandler:
'stop screen flicker
Application.ScreenUpdating = False
'set the variables
Set Rng = Sheet7.Range("B8")
DateBegin = Format(Sheet12.Range("B2").Value, "mm/dd/yy")
DateEnd = Format(Sheet12.Range("C2").Value, "mm/dd/yy")
'check the dates if all is OK run the filter
If Sheet12.Range("B2").Value >= Sheet12.Range("C2").Value Then
MsgBox " Your start value is wrong"
Exit Sub
Else
If Not IsEmpty(DateBegin) And Not IsEmpty(DateEnd) Then
'run the filter
With Rng
.AutoFilter Field:=3, Criteria1:=">=" & DateBegin, _
Operator:=xlAnd, Criteria2:="<=" & DateEnd
'copy values
CopyFilter
'show all data
Showall
End With
End If
End If
'error block
'On Error GoTo 0
Exit Sub
'errHandler:
MsgBox "There is no data"
Showall
End Sub


This question generated 11 answers. To proceed to the answers, click here.

This thread is current as of August 02, 2017.


For more resources for Microsoft Excel