ADVERTISEMENT
Sub FilterA1()
If Range("A1").Value <> "All" Then
ActiveSheet.AutoFilter.Range.AutoFilter Field:=1, Criteria1:=Range("A1").Value
Else
ActiveSheet.AutoFilter.Range.AutoFilter Field:=1
End If
End Sub
Private Sub CommandButton1_Click()
Dim lst As Long
Dim crit As Variant
Dim clrows As Long
With Application
.ScreenUpdating = False
With Sheet1
clrows = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A7:D" & clrows).Clear
crit = .TextBox1.Value
End With
With Sheet2
lst = .Range("A" & Rows.Count).End(xlUp).Row
.Range("$A$1:$D$" & lst).AutoFilter Field:=1, Criteria1:=crit
.Range("$A$1:$D$" & lst).SpecialCells(xlCellTypeVisible).Copy Sheet1.Range("A7")
.Cells.AutoFilter
End With
Sheet1.TextBox1.Value = ""
.ScreenUpdating = True
End With
End Sub