Autofilter VBA Help!

[longytravel]

Afternoon,

Wondering if someone could help.

I have the following code that I have put together with the help from various people

I would like to remove the Autofilter element - i am simply looking to have a userform which will search for infomration between two dates. It will then copy and paste the info from between the two dates to a new sheet which i have labelled 'Reports'.

By having the autofilter element in it filters the info of the 'data' sheet which is not needed

Look forward to help as ever!

Private Sub CommandButton1_Click()
    Date1 = Me.Date10.Text
    Date2 = Me.Date20.Text
    With Sheets("data")
        lr = .Cells(Rows.Count, "B").End(xlUp).Row
        With .Range("B1")
            .AutoFilter
            .AutoFilter Field:=2, Criteria1:=">=" & Date1, Operator:=xlAnd _
            , Criteria2:="<=" & Date2
        End With
        .Range("B1:B" & lr).EntireRow.Copy Sheets("reports").Range("A1")
    End With
End Sub
Private Sub Date10_AfterUpdate()
If Not IsDate(Me.Date10.Value) Then
    Me.Date10.Text = ""
    MsgBox "please type a valid date!"
    Exit Sub
End If
End Sub
Private Sub Date20_AfterUpdate()
If Not IsDate(Me.Date20.Value) Then
    Me.Date20.Text = ""
    MsgBox "please type a valid date!"
    Exit Sub
End If
End Sub

 

[jbeaucaire]

I believe the Autofilter IS the way to do this. The only thing missing from your first macro is turning the filter off when you're done.

Private Sub CommandButton1_Click()
    Date1 = Me.Date10.Text
    Date2 = Me.Date20.Text
    With Sheets("data")
        LR = .Cells(Rows.Count, "B").End(xlUp).Row
        .Range("B1").AutoFilter
        .Range("B1").AutoFilter Field:=2, Criteria1:=">=" & Date1, Operator:=xlAnd, _
            Criteria2:="<=" & Date2
        .Range("B1:B" & LR).EntireRow.Copy Sheets("reports").Range("A1")
        .AutoFilterMode = False
    End With
End Sub