Help With Combox VBA

[longytravel]

Hi,

i have some code that searches by date (text box - command button 1) and project name (combobox1 - command button 3). It takes info from the 'data' sheet to the 'reports' sheet

i now also have a second combo box (command button 7) that i want to use to search

i have copied the vba from the first combobox and just changed things to reflect the different columns but i cant get it to work

it copies over the headers but no detail from underneath.

any ideas?

Thanks as ever

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
        .Range("B1").AutoFilter Field:=2, Criteria1:=">=" & Date1, Operator:=xlAnd, _
            Criteria2:="<=" & Date2
        .Range("A1:I" & lr).SpecialCells(xlCellTypeVisible).Copy Sheets("reports").Range("B2")
        .AutoFilterMode = False
    End With
 
    Range("A:A").ColumnWidth = 5
    Range("B:B").ColumnWidth = 20
    Range("C:C").ColumnWidth = 10
    Columns("D:I").ColumnWidth = 20
    Range("J:J").ColumnWidth = 10
 
End Sub
 
Private Sub CommandButton3_Click()
    With Sheets("data")
        lr = .Cells(Rows.Count, "A").End(xlUp).Row
        .Range("A1").AutoFilter
        .Range("A1").AutoFilter Field:=1, Criteria1:=Me.ComboBox1
        .Range("A1:I" & lr).SpecialCells(xlCellTypeVisible).Copy Sheets("reports").Range("B2")
        .AutoFilterMode = False
    End With
 
    Range("A:A").ColumnWidth = 5
    Range("B:B").ColumnWidth = 20
    Range("C:C").ColumnWidth = 10
    Columns("D:I").ColumnWidth = 20
    Range("J:J").ColumnWidth = 10
 
End Sub
 
Private Sub CommandButton7_Click()
    With Sheets("data")
        lr = .Cells(Rows.Count, "I").End(xlUp).Row
        .Range("I1").AutoFilter
        .Range("I1").AutoFilter Field:=1, Criteria1:=Me.ComboBox2
        .Range("A1:I" & lr).SpecialCells(xlCellTypeVisible).Copy Sheets("reports").Range("B2")
        .AutoFilterMode = False
    End With
 
    Range("A:A").ColumnWidth = 5
    Range("B:B").ColumnWidth = 20
    Range("C:C").ColumnWidth = 10
    Columns("D:I").ColumnWidth = 20
    Range("J:J").ColumnWidth = 10
 
End Sub

 

[AlphaFrog]

.Range("I1").AutoFilter Field:=9, Criteria1:=Me.ComboBox2

 

[AlphaFrog]

Private Sub CommandButton7_Click()
    With Sheets("data")
        lr = .Cells(Rows.Count, "I").End(xlUp).Row
        .Range("I1").AutoFilter
        .Range("I1").AutoFilter Field:=[COLOR="Red"]9[/COLOR], Criteria1:=Me.ComboBox2
        .Range("A1:I" & lr).SpecialCells(xlCellTypeVisible).Copy Sheets("reports").Range("B2")
        .AutoFilterMode = False
    End With

 

[longytravel]

Ha ha!
Great stuff!
AlphaFrog comes to the rescue again!

Thanks