VBA - Autofilter - Not Working!

[longytravel]

Morning,

I have this code and that has been brought together with some help from this board!

Unfortunalty, it isnt quite working - when i search for information between two dates using a Userform it only pulls accross the headings (from the sheet 'data' - the sheet 'reports' - it doesnt pull accross any actually data underneath

Any ideas?

Also, would be great if i could paste it into B2 rather than A1 - just so it looks better?

Any help greatly appriciated!

Cheers

 

[longytravel]

helps if i attach the code!!

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
 
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

 

[AlphaFrog]

Change this line ...

.Range("B1:B" & lr).EntireRow.Copy Sheets("reports").Range("A1")

To something like this...

.Range("[COLOR="Red"]A[/COLOR]2:[COLOR="Red"]G[/COLOR]" & lr).SpecialCells(xlCellTypeVisible).Copy Sheets("reports").Range("B2")

Change the red letters to the columns of filtered data that you want to copy.

 

[longytravel]

Thanks Alphafrog,

I'm afraid that doesnt work??

Just get the debug message with that line highlighted - if it helps the headings are A1 : I1
data below will be a list that grows over time but will remain between A and I

Thanks for looking at this for me

 

[longytravel]

Oh wait, it pulls accross the headings again but no details
Copies to B2 whihc is great!!

 

[AlphaFrog]

Change this...

    Date1 = Me.Date10.Text
    Date2 = Me.Date20.Text

To this...

    Date1 = CLng(DateValue(Me.Date10.Text))
    Date2 = CLng(DateValue(Me.Date20.Text))

 

[longytravel]

Impressive stuff!
Thank you - works like a dream!

Last question i promise........

I can now search for information based on the date - on the same userform i would like to search via the 'project name'.

I have a combobox(1) that takes all the project names from a dynamic named range.

What would that code look like? The project names are all in column A on the 'data' sheet - again if i can paste to B2 on the 'reports' sheet that would be great!

Thanks again

 

[AlphaFrog]

Private Sub CommandButton1_Click()
    Date1 = CLng(DateValue(Me.Date10.Text))
    Date2 = CLng(DateValue(Me.Date20.Text))
    With Sheets("data")
        LR = .Cells(Rows.Count, "B").End(xlUp).Row
        .Range("B1").AutoFilter
        [COLOR="red"].Range("A1").AutoFilter Field:=1, Criteria1:=Me.ComboBox1[/COLOR]
        .Range("B1").AutoFilter Field:=2, Criteria1:=">=" & Date1, Operator:=xlAnd, _
            Criteria2:="<=" & Date2
        .Range("A2:I" & LR).EntireRow.Copy Sheets("reports").Range("B2")
        .AutoFilterMode = False
    End With
End Sub

 

[longytravel]

Thanks Alphafrog - unfortunalty that isnt working

I have a second button under the combobox that i want to use this feature with. Will your code not only search for project but you could filter by date also?!

 

[AlphaFrog]

Change the name of the command button (red).

Private Sub [COLOR="Red"]CommandButton?[/COLOR]_Click()
    With Sheets("data")
        LR = .Cells(Rows.Count, "A").End(xlUp).Row
        .Range("A1").AutoFilter
        .Range("A1").AutoFilter Field:=1, Criteria1:=Me.ComboBox1
        .Range("A2:I" & lr).SpecialCells(xlCellTypeVisible).Copy Sheets("reports").Range("B2")
        .AutoFilterMode = False
    End With
End Sub